SELECT INSERT SQL Statement

[Miko]

Hello,

 

I'm trying to make an SQL statement that insert data when it isn't found with a SELECT, and this in 1 query

 

I have this for the moment:

 

INSERT INTO checkedimage
(quality, date, depot, tour, image_name, year, month, day, name_ok) 
SELECT * FROM checkedimage WHERE image_name NOT IN
(SELECT * FROM checkedimage), VALUES('y', 20091019, 0530, 'T002', '03-RUDI VAN LAER')

 

I'm doing wrong here, but don't know what

[Mchl]

NOT IN requires that subselect returns one column only. Also it's not the most efficent way of doing this, but we can deal with this later.

 

You also need to remove VALUES() part, as you're using INSERT...SELECT

[cags]

I'm not entirely sure on the objective, but I don't believe this can be done in one query, at least not in the manner you appear to be attempting. You will probably have to first run a SELECT query to fetch any rows that match your criteria and if it doesn't find any rows then run an INSERT query. Alternatively it's possible that you could make use of the INSERT ON DUPLICATEsyndax, but I think in your situation this is unlikely, as it will update the column if it already exists whereas I believe you wish to not update.

 

Edit: Well they say you learn something every day. You may also be able to use something along the lines of...

 

INSERT IGNORE INTO...

 

Which will just not insert if a key field will be duplicated.