Updating a column from one row to the other

Question

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

Possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — techspider, Oct 10 '16 at 19:42

score 0 · Accepted Answer · answered Oct 10 '16 at 20:29

0

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'

answered Oct 10 '16 at 20:29

Nirjhar Vermani

1,215
8
17

Updating a column from one row to the other

1 Answers1