question

EvansGxz-8658 avatar image
0 Votes"
EvansGxz-8658 asked MelissaMa-msft commented

IF EXIST UPDATE, IF NOT EXIST INSERT

Hi, i want to know how update a row if exist and if don't exist insert a new row
Example:

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Is it supposed to be used in a multi-tasking environment (when more programs access the table in parallel)?

0 Votes 0 ·

Hi @EvansGxz-8658,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

If you only want to update one row of data in a table, you can use IF statement:

 IF EXISTS (SELECT 1 FROM Tbl WHERE UniqueColumn = 'Something')
 BEGIN
     UPDATE Tbl 
     SET ...
     WHERE UniqueColumn = 'Something';
 END
 ELSE
 BEGIN
     INSERT INTO Tbl
     SELECT ...
 END

If you want to INSERT / UPDATE a destination table from on a source table, you can use a MERGE statement:

 MERGE Tbl2 AS t
 USING (
     SELECT * FROM tb1
 ) AS s ON t.UniqueColumn = s.UniqueColumn
 WHEN MATCHED THEN
     UPDATE SET
         t.Col1 = s.Col,
         ...
 WHEN NOT MATCHED THEN
     INSERT (
         Col1,
         Col2,
         ...
     )
     VALUES (
         s.Col1,
         s.Col2,
         ...
     );



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @EvansGxz-8658,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Please also refer below one simple example and hope it could be helpful to you.

 --DDL and insert sample data
 drop table if exists test
    
 create table test
 (Testno int primary key,
 name varchar(20),
 age int)
    
 insert into test values
 (111,'Ann',19),
 (222,'Bob',20),
 (333,'Cathy',21)
    
 select * from test

Using exists as below:

 UPDATE T SET   --update
     name = 'Amy',
     age = 19
 FROM
     test AS T
 WHERE
     T.testno = 111
    
 INSERT INTO test (    --insert
     Testno,
     name,
     age)
 SELECT
     id = 555,
     name = 'Elan',
     age = 19
 WHERE
     NOT EXISTS (SELECT 1 FROM test AS T WHERE Testno = 555)

Or using Merge statement as mentioned by Guoxiong.

Best regards
Melissa


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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.