Delete from db with a table join

Databases
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
                       INNER 
                           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'
	                    )

bazz

2

groan: that errors out.

execute failed: You can’t specify target table ‘file_sequencer’ for update in FROM clause at Pages line 718.

3 
DELETE file_sequencer
  FROM file_sequencer
INNER
  JOIN file_dates
    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 = ?
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()

bazz

5

ok, I was close. should’ve refreshed my sitepoint page before posting.

Thanks rudy.

6

Get rid of the .*

7

actually in mysql it’s valid

8

Interesting… valid, but not necessary, I assume?

9

that’s what the square brackets mean, optional –