ibazz — 2011-03-19T22:15:44-04:00 — #1
I have a parent table and a child table.
For my delete, the details I need to relate to are spread across the two tables.
parent table holds the business id and content_category whilst the child table stores the relevant dates.
does this query structure (with a sub-select), fit the bill or is there a simpler more efficient way? Is there a principle that can apply to the decision-making process?
I dislike asking for help with vaguely or quite similar queries when I reckon there must be a rule or principle which 'dictates' key criteria with regard to sub selects or specific joins etc.
delete from file_sequencer
WHERE file_id IN ( SELECT file_id
FROM file_sequencer as fs
JOIN file_dates as fd
ON fd.file_id = fs.file_id
WHERE fs.business_id = ?
AND fs.content_category = ?
AND fd.live_to < ? #live_from
AND live_to != '0000-00-00'
ibazz — 2011-03-19T22:25:34-04:00 — #2
groan: that errors out.
execute failed: You can't specify target table 'file_sequencer' for update in FROM clause at Pages line 718.
r937 — 2011-03-19T23:28:55-04:00 — #3
ON file_dates.file_id = file_sequencer.file_id
AND file_dates.live_to < ? #live_from
AND file_dates.live_to <> '0000-00-00'
WHERE file_sequencer.business_id = ?
AND file_sequencer.content_category = ?
ibazz — 2011-03-19T23:31:53-04:00 — #4
oh... I seem to have got it. well, it seems to work.
Is this correct/best?
delete file_sequencer.* from file_sequencer
inner join file_dates as fd
on fd.file_id = file_sequencer.file_id
where file_sequencer.business_id = ?
and file_sequencer.content_category = ?
and fd.live_to < ? #live_from
and fd.live_to != '0000-00-00'
and fd.live_to < curdate()
ibazz — 2011-03-19T23:33:31-04:00 — #5
ok, I was close. should've refreshed my sitepoint page before posting.
system — 2011-03-20T10:16:45-04:00 — #6
r937 — 2011-03-20T14:18:58-04:00 — #7
actually in mysql it's valid
system — 2011-03-20T14:25:58-04:00 — #8
Interesting... valid, but not necessary, I assume?
r937 — 2011-03-20T14:33:51-04:00 — #9
that's what the square brackets mean, optional --