Click here to Skip to main content
15,860,943 members
Please Sign up or sign in to vote.
2.09/5 (4 votes)
See more:
Hi all
I want to insert a new record into my table if does not exist.
When I write this code for example:

insert into tablename (code) values ('1448523')
WHERE not exists(select * from tablename where code='1448523')


I get an error

Incorrect syntax near the keyword WHERE

When I use if not exists again I have an error.
How do I to insert new record in my table if not exists?
Posted
Updated 7-Jul-21 22:08pm
v2
Comments
Keith Barrow 27-Feb-11 6:25am    
I put your sql in pre tags to format it better, and fixed some minor grammar problems too.

Yout Sql command is Incorrect , Insert Command doesn't have Where clause.

Where Clause is applicable to Update, Select and Delete Commands

SQL
insert into tablename (code) values ('1448523')
WHERE not exists(select * from tablename where code='1448523') --incorrect in insert command


you have two ways:

1.
SQL
If Not Exists(select * from tablename where code='1448523')
Begin
insert into tablename (code) values ('1448523')
End


2.
SQL
insert into tablename (code)
 Select '1448523' Where not exists(select * from tablename where code='1448523')
 
Share this answer
 
Comments
Keith Barrow 27-Feb-11 6:34am    
Actually, the OP's command is valid with some other database engines such as MySQL.
_Ashish 27-Feb-11 11:11am    
MSSQL server doesn't support this way
mondo3 23-Sep-11 18:08pm    
for number 2., I think you need to add "From tablename" right before the "Where"
mondo3 23-Sep-11 18:09pm    
so it should be:
insert into tablename (code)
Select '1448523' From tablename Where not exists(select * from tablename where code='1448523')
see here[^]
 
Share this answer
 
I had the sam problem and after numerous trials I succeeded with following formula:
insert into tablename (code) (Select '1448523' from dual Where not exists(select code from tablename where code='1448523'));
 
Share this answer
 
Comments
CHill60 17-Sep-19 4:08am    
No different to the solution already posted - except of course that this is for Oracle
Member 13329050 23-Apr-21 2:17am    
Hi, it helped me plenty. Coz when i didnt use "from tablename" it didnt work at all. And when I used "from tablename" it tried to insert it for each record in that table, that is why it always failed due to unique constraint on thet column.
When I used "from dual", it worked perfectly. Thanks.
If you run this:
insert into tablename (code)
Select '1448523' Where not exists(select * from tablename where code='1448523')
2 things are incorrect for MYSQL
1. you need to add FROM tablename after the Select '1448523'
2. it will insert an entry for every row that exists in the table that does not contain 1228523

It does work if you do this:
INSERT INTO tablename (code)
SELECT MAX('1448523') FROM tablename WHERE NOT EXISTS(SELECT * FROM tablename WHERE code='1448523');

That searches, if it finds one entry it wont insert, if it finds none it inserts one entry.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900