MYSQL多表删除

I have 2 tables setup like this:

Items:
   id (int)
   name (varchar)
   category (int)
   last_update (timestamp)

Categories
   id (int)
   name (varchar)
   tree_id (int)

I want to delete all the records from Items, who's last_update is NOT today, and who's corresponding categories.tree_id is equal to 1. However, I don't want to delete anything from the Categories table, just the Items table. I tried this:

$query = "DELETE FROM items USING categories, items
    WHERE items.category = categories.id
    AND categories.tree_id = 1
    AND items.last_update != '".date('Y-m-d')."'";

However this just seems to delete EVERY record who's tree_id is 1. It should keep items with a tree_id of 1, as long as their last_update field is today

What am I missing?

You say that last_update contains time-stamps - I assume UNIX time-stamp. You can never find a record with the same time-stamp as when the stamp was executed and you can not compare a time-stamp with formatted date, they'll never correspond. So you need to store the data in last_update column in a date(Y-m-d) format so that compare those which are not equal.

If last_update is a timestamp field,and you are only passing in a date (with no time component) in your where clause, you are, in essence, actually doing this (if passing in 2012-10-24 for example):

AND items.last_update != 2012-10-24 00:00:00

This means every row without that exact second value in the timestamp would be deleted. You are much better doing something like

AND items.last_update NOT LIKE '".date('Y-m-d')."%'";

Of course you want to make sure you have an index on last_update.

Or if you don't care about index performance on last_update field (i.e. you are just doing this as a one off query and don't want to index this field), you could do this, which may make more logical sense to some

AND DATE(items.last_update) <> '".date('Y-m-d')."'"

The bottom line is that you need to only be comparing the date component of the last_updated field in some manner.

Sounds like you need a subquery

DELETE FROM items USING categories, items
    WHERE items.category = categories.id
    AND items.last_update != '".date('Y-m-d')."'"
    AND items.id in 
    (
      SELECT id from items inner join categories on item.category = categories.id
    )