0

MySQL version 5.5.37 using phpmyadmin

I have a problem with deleting the ID’s from 2 tables.

The tables are jos_users and jos_vm_user_info

I need to delete the id's from both tables using the ‘registered before‘ date and ‘last visit’ date in jos_users

If I run this -

DELETE jos_users,jos_vm_user_info from jos_users,jos_vm_user_info WHERE jos_users. `registerDate` < '2012-12-31 23:59:59' AND `lastvisitDate` < '2012-12-31 23:59:59'

I get ‘0 rows deleted’. I would expect over 2000 rows to be deleted

I’ve obviously got something wrong there but I don’t enough about what I’m doing

or is there a better way to do it?

BTW a SELECT using the table and conditional statements works fine

SELECT * FROM `jos_users` WHERE  `registerDate` < '2012-12-31 23:59:59' AND `lastvisitDate` < '2012-12-31 23:59:59'

It’s getting the id's deleted in the the jos_vm_user_info table that’s the problem.

Stingraynut
  • 85
  • 3
  • 9
  • asuming these two tables have foreign key relationship, you could use cascade delete to delete the entries in both tables – Markus Sep 09 '14 at 07:40
  • 1
    Assuming there is a relationship between the tables(?) then I'd set a Foreign Key in the second table and `ON DELETE CASCADE`. Then deleting it from the first table will result in the row also being removed from the table that references it. – adey_888 Sep 09 '14 at 07:40
  • possible duplicate of [mysql delete on join?](http://stackoverflow.com/questions/3402403/mysql-delete-on-join) – Mikpa Sep 09 '14 at 07:40

3 Answers3

0

You have incorrect DELETE syntax..

example of delete syntax:

DELETE FROM tbl_name WHERE 1>0;

Your delete syntax contain part DELETE some_column FROM....

that highlighted part is wrong..

Lukas Caniga
  • 399
  • 6
  • 17
  • Actually, I think they might be specifying table names, not columns. You can do multi-table deletes in this way - see my answer. – adey_888 Sep 09 '14 at 07:44
0

http://dev.mysql.com/doc/refman/5.6/en/delete.html

Look up Multi-Table Deletes on that page.

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause.

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
adey_888
  • 316
  • 1
  • 6
0

Thanks everyone for the answers, much appreciated. It all helps me to understand sql better. I realised that the best way to achieve the result I want, which is to remove fake users, was to remove all id's that were not in the orders table. I found the answer here Delete sql rows where IDs do not have a match from another table I removed 6000 id's from a total of 10,000 !

Community
  • 1
  • 1
Stingraynut
  • 85
  • 3
  • 9