CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

SQL Server Update data if not exists from another table

I need to update values in a table from another table and if they don't exist it must be inserted. So I saw this example in here

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

Now I would like to do this but instead of a where clause with a value i need to check if columns match on another table.

Something like

    UPDATE t1
        SET 
            t1.status = t2.status
        FROM 
            table1 t1, table2 t2
        WHERE t1.reference = t2.reference and t1.section = t2.section

I assume this is very bad in terms of performance and i would like to hear some other suggestions.

EDIT

Also please, if you can, i would like to hear why the approach you give is better than using a merge or a join for example. Much appreciated.

RESULT

Here is the approach i went for after checking how bad merge performance is compared to an update then insert and trying to test everyones approach this is what i decided for, basically uses a bit of everyone's answer 

        UPDATE C
        SET 
            C.ConsumoWeek01 = M.ConsumoWeek01
        FROM 
            Consumos AS C 
        INNER JOIN @tempTable M
        ON C.Referencia = M.Referencia 
        AND C.UAP = M.UAP

        INSERT INTO 
            Consumos
        SELECT *
        FROM @tempTable M
        WHERE NOT EXISTS
        (
            SELECT 1 FROM Consumos C
            WHERE C.Referencia = M.Referencia 
            AND C.UAP = M.UAP
        )

Upvotes: 0

Views: 3563

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Your method is fine just use standard explicit join syntax instead of old style with comma separate : 

update t1
     set . . . 
from table t1 inner join
     table t2
     on t1.reference = t2.reference and t1.section = t2.section;

Upvotes: 1

Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

Use merge statement: 

MERGE INTO Table1 T1 
USING 
(
    SELECT * FROM Table2 
) T2
ON 
(
    T1.reference = T2.reference AND 
    T1.section = T2.section
)
WHEN MATCHED THEN 
--UPDATE STATEMENT 
WHEN NOT MATCHED THEN 
--INSERT STATEMENT

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use not exists

UPDATE t1
        SET 
            t1.status = t2.status
        FROM 
            table1 t1 where not exists( select 1 from  table2 t2 where t1.=ref=t2.ref and t1.section=t2.secction)

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Try this

UPDATE t1
        SET 
            t1.status = t2.status
        FROM 
            table1 t1
        WHERE NOT EXISTS (SELECT 1 FROM  table2 t2
                            WHERE t1.reference = t2.reference 
                                and t1.section = t2.section )

Upvotes: 2

Related Questions