• Register
0 votes
150 views

SQL check if record exists before insert

I need to write a stored procedure to update a row. If the row does not exist, I need to insert it. Any Idea?
1.9k points

Please log in or register to answer this question.

1 Answer

0 votes

SQL check if record exists before insert

You can use a left Join or Exists from the table source to the table destination on the ID column to check if you have a matching row or not. Try something like:

INSERT INTO Dst(Id,Data) SELECT Src.Id,Src.Data FROM Src
LEFT JOIN Dst ON Src.Id=Dst.Id
WHERE Dst.Id IS NULL -- not found in destination
UPDATE Dst SET Data=Src.Data FROM Src
LEFT JOIN Dst ON Src.Id=Dst.Id
WHERE Dst.Id IS NOT NULL -- found in destination (or use an INNER JOIN)

You can look at the MERGE statement. To find how to check data existence in multiple columns using Exists ()  in Where clause to make the record unique.

declare @name nvarchar(10) = 'A'
if exists (select * from Client where name = @name
)
update Client …
else
insert into Client ...

Here is another solution: INSERT ... ON DUPLICATE KEY UPDATE Statement

If you specify the ON DUPLICATE KEY UPDATE clause and a row to be inserted causes a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row happens. Example: if column a is declared as UNIQUE and has the value 1, the following two statements have the same effect:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

In the case of an auto-increment column, an INSERT statement increases the auto-increment value, but UPDATE does not. If column b is unique, the insert is equivalent to this UPDATE statement:

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 or b=2 matches several rows, then only one row is updated. You need to avoid using an ON DUPLICATE KEY UPDATE clause on tables with unique indexes.

I hope it helps!

3.4k points

Related questions

0 votes
1 answer 9 views
9 views
Problem : Sql check if record exists before insert.
asked 5 days ago Sifat55 90.2k points
0 votes
1 answer 21 views
21 views
Problem: I am trying to writre SQL server update and stuck in this. SQL server add column if not exists.
asked Mar 23 PkGuy 25.9k points
0 votes
1 answer 68 views
1 vote
1 answer 9 views
9 views
Problem: I am new to postgres sql and have been using postgres sql 9.4 When I try to create tables with this code : CREATE TABLE IF NOT EXISTS "Team"( "id" SERIAL, "name" varchar(50) NOT NULL, "description" varchar(255) ); CREATE TABLE IF NOT EXISTS "Member"( " ... .java:1998) ~[org.postgresql.postgresql-9.4-1201-jdbc41.jar:9.4] ...Why I’m getting this error and how can I solve this ?
asked Apr 18 Raj Tyagi 16k points
0 votes
1 answer 11 views
11 views
Problem: Sql select rows with same values in one column.
asked Jul 4 Priscilla Gurpreet 62.6k points
0 votes
1 answer 13 views
13 views
Problem: Hello! I am wondering that what if someone needs to insert the data from one table to another table? Is there any way to do that in SQL? I am not talking about copying the entire data from one table to another but the specific records. Please answer.
asked Mar 22 Code Learner 10.5k points
0 votes
1 answer 10 views
10 views
Problem: Is there someone who can help me solve this problem “Write a query to insert any 2 records into the role table”?
asked Mar 30 tuhin1 48.9k points
0 votes
1 answer 12 views
12 views
Problem: How does php mysql create table if not exists? can you give me a soluation
asked Apr 13 jamuna1 30.2k points
1 vote
1 answer 13 views
13 views
Problem: Can someone please help fix this uncaught error : postgres create table if not exists
asked Apr 1 MUHAMMAD MUNEEB 83k points
1 vote
1 answer 11 views
11 views
Problem: How can I deal with this problem : postgresql create table if not exists
asked Apr 1 MUHAMMAD MUNEEB 83k points