Update query question

[Zac5]

Hi,
How can I write this correctly in order to achieve this:

update dbo.table1
set B.table1field1=A.table1field1
from dbo.table1 as A,dbo.table1 as B
where A.table1field2=10 and B.table1field2=10 and
A.table1field3=1 and B.table1field3=2

currently I get the following error:

Server: Msg 1032, Level 15, State 1, Line 4
Cannot use the column prefix 'B'. This must match the object in the UPDATE clause 'dbo.table1'.

 

[gmmastros]

update [red]B[/red]
set    B.table1field1=A.table1field1
from   dbo.table1 as A,dbo.table1 as B
where  A.table1field2=10 
       and B.table1field2=10 
       and A.table1field3=1 
       and B.table1field3=2

You can only update 1 table at a time, so you must specify the table to update. Since you used aliases in your from clause, you must also use the alias in the update part.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Zac5]

this all needs to happen within the same table, set field1 values in table to be equal if field2=10 for both records and field3=1 for one record and field3=2 for the other where they have some other value in common.

 

[r937]

zac, i read that last statement of your three times and i still don't get it

could you please give an example by showing a row of data, before and after the update

r937.com | rudy.ca