Dr Herbie wrote:I have a database server with two databases : a live database and a test database. The test database is essentially an old version of the live database.
The live database has been updated with some live data on some records. Only one column has been updated.
I want to copy the new data into the test database, only updating the single updated field in the relevant rows.
I can't remember the syntax for this, nor can I find an answer on Google (I must be using the wrong term for this or something).
EDIT: I'm hoping for something like:
UPDATE [Test].Table set field = (select field from [Live].Table where [Live].Table.Field = [Test].Table.Field)
UPDATE [Test].dbo.[Table1] SET [Test].dbo.[Table1].field = P.field FROM [Production.dbo.[Table1] P WHERE [Test].dbo.[Table1].IDField = P.IDField AND [Test].dbo.[Table1].field <> P.field
Thanks! It was the dbo part of the fully qualified name I was missing!
I'm interested in the thread I found regarding the following syntax adn edited almost completely, but not sure what the P means...can you advise?
The two databases are every16_joom1 - database jos_contest - table id - field
and every16_web - datbase news - table article_id - field
UPDATE [every16_joom1].dbo.[jos_contest] SET [every16_joom1].dbo.[jos_contest].id = P.article_id FROM [every16_web.dbo.[news.article_id] P WHERE [every16_joom1].dbo.[jos_contest].id= P.IDField AND [every16_joom1].dbo.[jos_contest].field <> P.field
You can write it more verbose with an AS
FROM [every16_web.dbo.[news.article_id] AS P
[AS] table_alias Is an alias for table_source that can be used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is frequently a shortened table name used to refer to specific columns of the tables in a join. If the same column name
exists in more than one table in the join, SQL Server requires that the column name be qualified by a table name, view name, or alias. The table name cannot be used if an alias is defined.
Another way to do it is to use an updateable VIEW:
CREATE VIEW TestAndLive AS SELECT L.ValueField AS "Live", T.ValueField AS "Test", L.KeyField FROM [Live]..Table AS L INNER JOIN [Test]..Table AS T ON L.KeyField = T.KeyField
Now you can do
UPDATE TestAndLive SET Test = Live
SELECT * FROM TestAndLive
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.