how to update a table, that the value of colA is same as value of colB

Question

how to update a table, that the value of colA is same as value of colB,i tried

UPDATE table
SET colA = (SELECT top 1 colB
FROM table a
WHERE ID= a.ID)

or

UPDATE table
SET colA = colB

but an error msg shows:
(it is in chinese), it more or less means the return value of query is more than 1
Thankyou

Answer 1

You can use the Merge statement to update on a target table from a source table.

Refer: https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

In your case, try the below statement

MERGE Table1 AS TARGET  
USING Table1 AS SOURCE   
ON (TARGET.ID= SOURCE.ID)   
WHEN MATCHED   
THEN UPDATE SET TARGET.COLA= SOURCE.COLB;  

Also be noted that some times, you might need to have some other conditions to be matched before doing the update, In such cases you can add condition after When Matched Clause. See the example.

MERGE Table1 AS TARGET  
USING Table1 AS SOURCE   
ON (TARGET.ID= SOURCE.ID)   
WHEN MATCHED and Target.ID <>2  --you can add as many conditions here with Target and Source   
THEN UPDATE SET TARGET.COLA= SOURCE.COLB;  

Hope this helps

Answer 2

Hi @Martin Wang ,
Please try:

SELECT (case when colA = colB then colB  
              else colA  
              end) as new_colA  
FROM TableName  

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.