[thelist] SQL: Insert with FK subselect?
Francis Marion
francis.marion at sfroy.com
Wed Jun 15 12:34:45 CDT 2011
I have an SQL question that either cannot be done, or that only
someone better versed at SQL than I am can answer.
What I want to be doing is finding all records who do not have a
related record of a certain type, in order to add it if it doesn't
exist.
The query is fairly straight-forward:
table 1: ID (int, pk)
table2: meta_key (varchar), meta_value (varchar), post_id (varchar)
/*
SELECT the primary keys of records from table 1,
WHERE there is no record in a joined table 2 whose value is 'foo'
*/
SELECT DISTINCT p.ID
FROM table1 p
INNER
JOIN table2 m
ON m.post_id = p.ID
WHERE p.post_type = 'sfr_place'
AND p.ID NOT IN
(
SELECT k.post_id
FROM table2 k
WHERE k.meta_key = '_sfr_places_icon_id'
)
ORDER BY p.ID ASC
Now what I want to do is to do a batch insert without resorting to a
server side language. So my attempt is as follows:
/*
Note that this is a separate table into which I need to
insert the key from another table1
*/
INSERT
INTO table2
( `meta_key`
, `meta_value`
, `post_id`)
VALUES ( '_sfr_places_icon_id'
, 652
,
SELECT DISTINCT p.ID
FROM table1 p
INNER
JOIN table2 m
ON m.post_id = p.ID
WHERE p.post_type = 'sfr_place'
AND p.ID NOT IN
(
SELECT k.post_id
FROM table2 k
WHERE k.meta_key = '_sfr_places_icon_id'
)
ORDER BY p.ID ASC
)
;
This is returning an (unspecified) mysql error. What am I doing wrong,
and how can I fix this?
Or is it simply that I'm pushing too hard and it can't be done and
must resort
to a server-side language to loop it?
Thanks,
Frank
More information about the thelist
mailing list