CODEWITHSUNDEEP
mysqlsql
Lorenc
Lorenc

Reputation: 55

Deleting multiple rows from multiple tables

I have three tables:

`MEMBERS`
with
`NAME` varchar(24) UNIQUE KEY

`LAST_LOGGED_IN` int(11) - It is a timestamp!

`HOMES`
with
`OWNER` varchar(24) 

`CARS`
with
`OWNER` varchar(24)

I use InnoDB for these tables, now my actual question is: How do I remove rows within all the tables if the UNIX_TIMESTAMP()-MEMBERS.LAST_LOGGED_IN > 864000?

I'm trying to remove inactive members' rows, and this is the hardest thing yet. I have about 40K rows, and increasing. I clean it regularly with DELETE FROM MEMBERS WHERE UNIX_TIMESTAMP()-LAST_LOGGED_IN> 864000

Any of your help would be extremely grateful! Thanks!!

Upvotes: 2

Views: 124

Answers (2)

spencer7593
spencer7593

Reputation: 108400

If you have already removed rows from the MEMBERS table, and you want to remove the rows from the other two tables where the value of the OWNER column does not match a NAME value from any row in the MEMBERS table:

DELETE h.*
  FROM `HOMES` h
  LEFT
  JOIN `MEMBERS` m 
    ON m.`NAME` = h.`OWNER`
 WHERE m.`NAME` IS NULL

DELETE c.*
  FROM `CARS` c
  LEFT
  JOIN `MEMBERS` m 
    ON m.`NAME` = c.`OWNER`
 WHERE m.`NAME` IS NULL

(N.B. these statements will also remove rows from the HOMES and CARS tables the OWNER column as a NULL value.)

I strongly recommend you to run a test of these statements using a SELECT before you run the DELETE. (Replace the keyword DELETE with the keyword SELECT, i.e.

-- DELETE h.*
SELECT h.*
  FROM `HOMES` h
  LEFT
  JOIN `MEMBERS` m 
    ON m.`NAME` = h.`OWNER`
 WHERE m.`NAME` IS NULL

Going forward, if you want to keep these tables "in sync", you may consider defining FOREIGN KEY constraints with the ON CASCADE DELETE option.

Or, you can use a DELETE statement that removes rows from all three tables:

DELETE m.*, h.*, c.*
  FROM `MEMBERS` m
  LEFT
  JOIN `HOMES` h
    ON h.`OWNER` = m.`NAME`
  LEFT
  JOIN `CARS` c
    ON c.`OWNER` = m.`NAME`
 WHERE UNIX_TIMESTAMP()-m.`LAST_LOGGED_IN` > 864000

(N.B. the predicate there cannot make use of an index on the LAST_LOGGED_IN column. An equivalent predicate with a reference to the "bare" column will be able to use an index.

WHERE m.`LAST_LOGGED_IN` < UNIX_TIMESTAMP()-864000

or an equivalent:

WHERE m.`LAST_LOGGED_IN` < UNIX_TIMESTAMP(NOW() - INTERVAL 10 DAY)

For best performance, you would need indexes on both HOMES and CARS with a leading column of OWNER, e.g.

... ON `HOMES` (`OWNER`)
... ON `CARS` (`OWNER`)

Upvotes: 1

ExactaBox
ExactaBox

Reputation: 3395

I don't use InnoDB so I had to look it up, but it does appear to support Referential Integrity. If you set relationships and then turn on ON DELETE CASCADE, the database itself will enforce the rules... i.e., when you delete a Member, the DBMS will take care of deleting the associated Homes and Cars.

See here and here, they might help.

Upvotes: 1

Related Questions