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'
groan: that errors out.
execute failed: You can't specify target table 'file_sequencer' for update in FROM clause at Pages line 718.
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 = ?
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()
ok, I was close. should've refreshed my sitepoint page before posting.
actually in mysql it's valid
Interesting... valid, but not necessary, I assume?
that's what the square brackets mean, optional --