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

Martin Wang 41 Reputation points
2022-06-20T05:05:31.593+00:00

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

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,006 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. sreejukg 9,171 Reputation points
    2022-06-20T05:43:25.263+00:00

    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

    No comments

  2. Joyzhao-MSFT 15,361 Reputation points Microsoft Employee
    2022-06-21T02:01:43.27+00:00

    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.

    No comments