SQL Server: copy data from one column to another column?

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)

Please help

<strong>Edit:</strong> Comment from post: I just want to copy all the anamoly_id from amb.anamoly_fee to amb.anamoly_log_update. My code might be nonsensical. Please do review it.

Answer1:

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

Answer2:

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

ON t1.anamoly_id=t2.anomaly_id

please think about what you really want to do and change your description ..

Answer3:

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.

Answer4:

You need to provide other linkage between tables than t1.anamoly_id=t2.anomaly_id or the query will do nothing

Answer5:

merge into amb.anamoly_log_update as t1 using amb.anomaly_fee as t2 on t1.anamoly_id=t2.anomaly_id when matched then update set t1.anamoly_id = t2.anomaly_id

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:SQL Server: copy data from one column to another column?