Click here to Skip to main content
14,362,722 members
Rate this:
Please Sign up or sign in to vote.
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 16-Sep-19 8:27am
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.
Rate this:
Please Sign up or sign in to vote.

Solution 1

see here[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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

Where Clause is applicable to Update, Select and Delete Commands

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


you have two ways:

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


2.
insert into tablename (code)
 Select '1448523' Where not exists(select * from tablename where code='1448523')
   
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')
Rate this:
Please Sign up or sign in to vote.

Solution 4

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'));
   
Comments
CHill60 17-Sep-19 4:08am
   
No different to the solution already posted - except of course that this is for Oracle

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100