Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')