update data from select

Question

Hi team,

I would like to ask a question. I want to insert a statement into a table, currently the way I know is using insert.....select.....

like INSERT INTO SQ1(COL1,COL2) SELECT CO1,CO2 FROM SQ2 WHERE LIQ='MAD',

At this point If I want to update this table with the values of other tables, without using update TABLE SET...FROM ....JOIN NEW_TABLE ...,

Is there any other way to achieve this, I have seen cte before, can anyone give me I'll explain.

Thanks in advance.

Answer 1

Hi,@SPWGUT

Have you heard of the use of merge into? It seems like a simple question. It's my word. I'm willing to do it.

MERGE INTO YourTable T  
   USING (  
          SELECT id, col1, col2   
            FROM other_table   
           WHERE tsql = 'MAD'  
         ) S  
      ON T.id = S.id  
WHEN MATCHED THEN  
   UPDATE   
      SET col1 = S.col1,   
          col2 = S.col2;  

As for cte , it doesn't mean that there is something special about it . Better call it, untested but should do what you want, hope this helps you.here is the code:

WITH CTE  
     AS (SELECT T1.Col1,  
                T2.Col1 AS _Col1,  
                T1.Col2,  
                T2.Col2 AS _Col2  
         FROM   T1  
                JOIN T2  
                  ON T1.id = T2.id  
         WHERE EXISTS(SELECT T1.Col1,  
                             T1.Col2  
                       EXCEPT  
                       SELECT T2.Col1,  
                              T2.Col2))  
UPDATE CTE  
SET    Col1 = _Col1,  
       Col2 = _Col2;  

As a reminder, if you want to learn merge usage, please refer to the documentation.

Bert Zhou

Answer 2

Hi @SPWGUT
You can use a triggle， whenever a row is inserted in this table, the trigger will be executed，so you can do some action to another table.

For details ，you can refer to this thread：https://www.aspsnippets.com/Articles/SQL-Server-Insert-data-to-another-Table-using-Insert-Update-and-Delete-Triggers.aspx

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and 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.