IF EXIST UPDATE, IF NOT EXIST INSERT

Question

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

Answer 1

Hi @EvansGxz ,

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.

Answer 2

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,  
		...  
	);