asked on

SQL 2008 syntax

I have to revert the changes I made to one of the field s in the table.

my production database name db1 and backup database - db2 both have table customer with custnum indexed and field1 which I have to replace data in production with data from field1 of db2. I am looking for exact syntax.
update db1 ........

Vitor Montalvão

UPDATE db1.dbo.customer
SET field1 = (SELECT field1 FROM db2.dbo.customer WHERE db2.dbo.customer.custnum = db1.dbo.customer.custnum)

Open in new window

leop1212

ASKER

I am getting errors
could you show same using alias db1 a , db2 b?

Msg 208, Level 16, State 1, Line 1
Invalid object name 'db2.customer'.

Vitor Montalvão

Of course db1 and db2 need to be replaced by the real names of the databases. As well the "fiedl1" should be replaced with the real column name since you didn't provide those name I just used some arbitrary names.
Here is the version with alias:

UPDATE db1.dbo.customer a
SET a.field1 = (SELECT b.field1 FROM db2.dbo.customer b WHERE b.custnum = a.custnum)

Open in new window

leop1212

ASKER

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Vitor Montalvão

That means the custnum is not unique. You need to provide the primary key column(s) to assure the uniqueness of the row.

leop1212

ASKER

there are two primary keys
primary-key.jpg

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leop1212

ASKER

perfect
thank you very much