PDA

View Full Version : mysql syntax error...



qmqmqm
March 11th, 2009, 03:00 PM
Hi

Does anyone know what is wrong with this query:

INSERT INTO `ps` (`p_id` , `p_name` , `description`) VALUES ('' , "PA" , "") WHERE NOT EXISTS (SELECT * FROM ps WHERE `p_name` = "PA" AND `description` = "")

Thanks.

Tom

kpatz
March 11th, 2009, 03:13 PM
Hi

Does anyone know what is wrong with this query:

INSERT INTO `ps` (`p_id` , `p_name` , `description`) VALUES ('' , "PA" , "") WHERE NOT EXISTS (SELECT * FROM ps WHERE `p_name` = "PA" AND `description` = "")

Thanks.

Tom
You can't use a where clause on an INSERT statement.

What exactly are you trying to accomplish? Insert a row if it doesn't already exist?

INSERT IGNORE INTO `ps` (`p_id` , `p_name` , `description`) VALUES ('' , 'PA' , '');

will insert the row if it doesn't already exist (do you have a primary key or unique index on the table? This is important for this to work)

kpatz
March 11th, 2009, 03:26 PM
Here's another way that does what I think you're after... which is to insert a row only if a row doesn't exist based on the where clause.



insert into ps(p_id,p_name,description) select * from (select '' as p_id,'PA' as p_name,'' as description) as c where not exists(SELECT * FROM ps as b WHERE p_name = 'PA' AND description = '');

qmqmqm
March 11th, 2009, 04:02 PM
You can't use a where clause on an INSERT statement.

What exactly are you trying to accomplish? Insert a row if it doesn't already exist?

INSERT IGNORE INTO `ps` (`p_id` , `p_name` , `description`) VALUES ('' , 'PA' , '');

will insert the row if it doesn't already exist (do you have a primary key or unique index on the table? This is important for this to work)


Hi Kpatz

The p_id field is the primary key.

Thanks,

Tom

qmqmqm
March 11th, 2009, 04:27 PM
Here's another way that does what I think you're after... which is to insert a row only if a row doesn't exist based on the where clause.

Thank you very much Kpatz!

kpatz
March 11th, 2009, 04:50 PM
The p_id field is the primary key.If it's an autoincrement field, you don't need to specify it in your insert.


insert into ps(p_name,description) select * from (select 'PA','') as c where not exists(SELECT * FROM ps as b WHERE p_name = 'PA' AND description = '');

qmqmqm
March 12th, 2009, 11:55 PM
If it's an autoincrement field, you don't need to specify it in your insert.

Great! Thanks Kpatz!