0

I need to delete from two tables, based on a query in one table

Table: entities guid: integer subtype: integer time_created: integer (Unix timestamp)

Table: objects_entity guid: integer title: text

guid in objects_entity is a foreign key to entities.guid

I need to delete related records in both tables based on subtype=17 and time_created is older than 14 days in entities (so also delete related objects_entity)

I'm very bad at SQL and by looking at examples I've created this:

DELETE entities, objects_entity FROM entities a INNER JOIN objects_entity b on b.guid = a.guid AND a.subtype =17 AND a.time_created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 14 DAY))

but this gives the error:

#1109 - Unknown table 'entities' in MULTI DELETE

which is beyond me, as a select statement identical to above works correctly.... The table exists.

Any ideas what's wrong with my syntax? Many thanks.

bobomoreno
  • 2,848
  • 5
  • 23
  • 42
  • possible duplicate of [delete from two tables in one query](http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query) – Marc B Jul 30 '12 at 18:32

2 Answers2

3

You need to replace

DELETE entities, objects_entity

with

DELETE a, b

since you are aliasing your tables with a and b.

Zane Bien
  • 22,685
  • 6
  • 45
  • 57
-1
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]

OR

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
John D
  • 2,307
  • 17
  • 28