user2797021 user2797021 - 1 month ago 6
SQL Question

Updating a column from one row to the other

I am trying to update orders table in MS SQL it has two rows with same order id but different release_num
I want to update the s_address1 field release 1 row with the data from release 2 joined by the order_id, I tried the following sql, but I get an ambiguous table error message

update orders
SET im1.s_address1=im2.s_address1
FROM orders im1
INNER JOIN orders im2
ON im1.order_id=im2.order_id
WHERE im1.owner_id ='SHX' AND im2.owner_id='SHX'


Please help
Thanks

Answer
update im1 
SET im1.s_address1=im2.s_address1
FROM orders im1
INNER JOIN orders im2
ON im1.order_id=im2.order_id
WHERE im1.owner_id ='SHX' AND im2.owner_id='SHX'