CODEWITHSUNDEEP
sqlsql-server
Sarah
Sarah

Reputation: 302

if a row exists in sql it should not perform an update

i have a query that based on a condition updates a table with multiple rows.But every time i run the query it copies all the same data over again:

SET IDENTITY_INSERT table ON
 INSERT INTO table(ID,NameID,tag)
SELECT 
(SELECT MAX(ID) FROM table) + ROW_NUMBER()OVER (ORDER BY ID),

     50000,
     tag
FROM Table
WHERE NameID = 10000
SET IDENTITY_INSERT table OFF

so everytime the above query runs it copies the data with the nameID 10000 and puts it in a new rows with the nameID of 50000 and increments the ID. so if this query runs the first time it will work fine the second time it runs it does the following

ID   |NameId  |tag
10   |100000  |4589
15   |100000  |7879
16   |500000  |4589 // first run of query gets the data which i wanted
17   |500000  |7879
18   |500000  |4589 // second run off query adds the same data over again
19   |500000  |7879

how can i tell it to check if 50000 exists in the table dont do an insert?

Upvotes: 0

Views: 68

Answers (2)

anderson macedo
anderson macedo

Reputation: 1

I belive you can use "if not exists" this case

if not exists("condition") begin
"insert"
end

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I have no idea why you are turning identity insert off. That seems to defeat the purpose of an identity column. But, that is not central to your question.

You can use not exists:

INSERT INTO table (ID, NameID, tag)
    SELECT (SELECT MAX(ID) FROM table) + ROW_NUMBER()OVER (ORDER BY ID),
           50000, tag
    FROM Table t
    WHERE NameID = 10000 AND
          NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.NameId = t50000 AND t2.tag = t.tag);

Upvotes: 1

Related Questions