I have 2 SQL tables with same column label, Table1 and Table2.

I want to update Table1 with value of Table2 if there is a same value for an attribute.

The tables has CODE, POSITION, DESCRIPTION as columns.

I do this:

UPDATE Table1
SET CODE = Table2.CODE,
    POSITION= Table2.POSITION
FROM Table2
WHERE DESCRIPTION = Table2.DESCRIPTION 

It works, but if in the DESCRIPTION Value of Table2 is not present into Table1, I want to insert the entire row, in other words I need to do something like:

UPDATE IF DESCRIPTION EXISTS
ELSE INSERT

How can I do this?

CodePudding user response：

You can do this from first-principals in 2 steps

1. Update the existing values like you have done:

   UPDATE Table1
   SET 
         CODE= t2.CODE,
         POSITION= t2.POSITION
   FROM Table1 t1
   INNER JOIN Table2 t2 ON t1.DESCRITPION = t2.DESCRITPION 
   

2. Then you can insert the missing records

   INSERT INTO Table1 (CODE, POSITION, DESCRITPION)
   SELECT CODE, POSITION, DESCRITPION
   FROM Table2 t2
   WHERE NOT EXISTS (SELECT DESCRITPION 
                     FROM Table1 t1 
                     WHERE t1.DESCRITPION = t2.DESCRITPION)
   

Most RDBMS have a MERGE statement that allows you to combine these two queries into a single atomic operation, you'll need to check the documentation for your vendor, but an MS SQL Server MERGE solution looks like this:

MERGE INTO Table1 AS tgt
      USING Table2 AS src
      ON tgt.DESCRITPION = src.DESCRITPION
      WHEN MATCHED 
          THEN UPDATE SET CODE= src.CODE,
                          POSITION= src.POSITION
      WHEN NOT MATCHED THEN
          INSERT (CODE, POSITION, DESCRITPION)
          VALUES (src.CODE, src.POSITION, src.DESCRITPION)

An additional benefit to the MERGE clause is that it simplifies capturing the changed state into the OUTPUT clause which allows for some complex logic all while keeping the query as a single operation without having to manually create and track transaction scopes.