CODEWITHSUNDEEP
phpmysql
GGcupie
GGcupie

Reputation: 1003

INSERT if VALUES don't exists

I have been loosing my MIND with this. I have three tables that I have to join, but I need to insert into the database only IF the three columns do not have existing values. I have tried ON DUPLICATE KEY UPDATE, WHERE NOT EXISTS, and FINALLY and am trying INSERT IGNORE that last one is a result of nothing where as the others would insert even IF the same values existed.

I am trying to 

insert into TABLE name: set_colors (school_art_id, baseimage_id, sub_folder, layer)

school_art_id is from "id" another table called school_art baseimage_id is the "id" from a table called baseimage sub_folder is the folder name in baseimage.sub_folder layer is the value from baseimage.layer

This is the query that DOES NOT work

INSERT IGNORE INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
SELECT 'school_art.id', 'baseimage.id', 'baseimage.sub_folder', 'baseimage.layer' 
SET school_art_id='school_art.id', 
baseimage_id='baseimage.id', 
sub_folder='baseimage.sub_folder', 
layer=baseimage.layer

Can someone please tell me what am I doing WRONG here?

Upvotes: 1

Views: 238

Answers (3)

GGcupie
GGcupie

Reputation: 1003

Well I found the solution, in case anyone else comes across the same problem ( I put this in a class so the {$value} is just passed in when you call the class.

INSERT IGNORE INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
SELECT school_art.id, baseimage.id, baseimage.sub_folder, baseimage.layer
FROM school_art 
JOIN baseimage ON baseimage.base_folder = school_art.series_code 
WHERE baseimage.image_type = 'B' AND school_art.id = '{$value}' ORDER BY school_art.id"

Upvotes: 0

Marc B
Marc B

Reputation: 360842

Your query syntax is completely broken for the select portion. You're selecting a series of constant strings, and then for some reason break into 'SET' and do some assignments. This makes no sense whatsoever, since you don't SET a value in a select.

Your query should look something like

INSERT IGNORE INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
SELECT field1, field2, field3, field4
FROM some_other_table
WHERE ...

Upvotes: 2

genesis
genesis

Reputation: 50982

 INSERT IGNORE

should be 

 INSERT IGNORE INTO

however SET isn't possible to be used in SELECT or in INSERT INTO query

Upvotes: 0

Related Questions