CODEWITHSUNDEEP
sqlsql-servert-sql
Dan
Dan

Reputation: 5

SQL: IF NOT EXISTS INSERT then UPDATE

I know there are a lot of posts on this subject but this is slightly different. I have 2 tables variant_detail (vad) and variant_external_analysis (vaea). vaea is mainly empty and I need to update it for all lines in vad. If I insert in vaea and the line already exists the it duplicates the line and so breaks the database.

In testing on 1 line I have solved the problem with:

IF NOT EXISTS (SELECT vaea_vad_id FROM variant_external_analysis 
            JOIN variant_detail ON vad_id = vaea_vad_id 
            WHERE vad_variant_code = 16469)
BEGIN
    INSERT variant_external_analysis (vaea_vad_id) 
    SELECT vad_id
    FROM variant_detail
END

UPDATE variant_external_analysis

SET vaea_last_amended_on = GETDATE(), vaea_last_amended_by = 13, vaea_n_1 = 1

FROM variant_external_analysis
JOIN variant_detail ON vad_id = vaea_vad_id

WHERE vad_variant_code = 16469

This works perfectly UNTIL I remove the WHERE statements so that it effects the whole table. The code below UPDATEs correctly but now no longer INSERTS

IF NOT EXISTS (SELECT vaea_vad_id FROM variant_external_analysis

JOIN variant_detail ON vad_id = vaea_vad_id 

            WHERE vad_id = vaea_vad_id)
BEGIN
    INSERT variant_external_analysis (vaea_vad_id) 
    SELECT vad_id
    FROM variant_detail
END

UPDATE variant_external_analysis

SET vaea_last_amended_on = GETDATE(), vaea_last_amended_by = 13, vaea_n_1 = 1

FROM variant_external_analysis
JOIN variant_detail ON vad_id = vaea_vad_id

I am probably missing something simple.

Upvotes: 0

Views: 158

Answers (2)

Yuriy Tsarkov
Yuriy Tsarkov

Reputation: 2548

The code looks pretty similar to T-SQL so here is an example how to do it using MERGE in MS SQL Server:

merge variant_external_analysis vea
--using here left join (along with where condition) we'll get only rows 
--that are in variand_details but aren't in variant_external_analysis
using(select vd.vad_id 
  from variant_detail vd
  left join variant_external_analysis vaea on vd.vad_id = vaea.vaea_vad_id 
  where vaea.id is null) t --or any another column of vaea
on (vea.vaea_vad_id = t.vad_id)
when not matched then 
    insert (vaea_vad_id, vaea_last_amended_on, vaea_last_amended_by, vaea_n_1)
    values(t.vad_id, getdate(), 13, 1)
;

UPD To test clause with a single row:

  1. add a code as a return column in a using clause
  2. extend a when clause with a code condition
merge variant_external_analysis vea
    --using here left join (along with where condition) we'll get only rows 
    --that are in variand_details but aren't in variant_external_analysis
    using(select vd.vad_id, vd.vad_variant_code 
      from variant_detail vd
      left join variant_external_analysis vaea on vd.vad_id = vaea.vaea_vad_id 
      where vaea.id is null) t --or any another column of vaea
    on (vea.vaea_vad_id = t.vad_id)
    when not matched and t.vad_variant_code = 16469 then 
        insert (vaea_vad_id, vaea_last_amended_on, vaea_last_amended_by, vaea_n_1)
        values(t.vad_id, getdate(), 13, 1)
    ;

Upvotes: 2

h33
h33

Reputation: 1334

You have "vad_variant_code = vad_id = vaea_vad_id" in you where clause.

Upvotes: 0

Related Questions