I have two tables with the same column
anomaly_id. I want to copy the row of
anomaly_id from the first table to the second table using this code
UPDATE amb.anamoly_log_update SET anamoly_id = t2.anomaly_id FROM amb.anamoly_log_update t1 INNER JOIN amb.anomaly_fee t2 ON t1.anamoly_id=t2.anomaly_id
Even after I did that it shows 0 rows affected, when there is data in
amb.anomaly.fee (source table)
Edit: Comment from post: I just want to copy all the
amb.anamoly_log_update. My code might be nonsensical. Please do review it.
To copy the id from anomaly_fee to anamoly_log_update use :
INSERT INTO anamoly_log_update (anamoly_id)
SELECT anamoly_id FROM anomaly_fee
with both columns it looks like that:
INSERT INTO anamoly_log_update (anamoly_id,PID)
SELECT anamoly_id,PID FROM anomaly_fee
You only would copy the data if they where in both tables .. and then there is nothing update because you do not change the data => 0 rows affected
please think about what you really want to do and change your description ..
Does amb.anamoly_log_update contain at least one row corresponding to the anamoly_id that's present in amb.anamoly_fee? You are trying to join on two tables on anamoly_id.
You need to provide other linkage between tables than
t1.anamoly_id=t2.anomaly_id or the query will do nothing
merge into amb.anamoly_log_update as t1
using amb.anomaly_fee as t2
when matched then
update set t1.anamoly_id = t2.anomaly_id