mysql delete with cross table lookup

Question

SQL

DELETE FROM sex s, users u WHERE s.id = 195 and u.id = s.uid and u.sessionCheck = 'd986a074c7549c566bfed1d4ad7ca491'

Error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's, users u WHERE s.id = 195 and u.id = s.uid and u.sessionCheck = 'd986a074c7549' at line 1

So clearly the error is with the joins in the delete. But everything I have tried just won't work.

I am using Server version: 5.6.16 - MySQL Community Server (GPL)

This question was caused by a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. — Kermit, Apr 28 '14 at 13:21
http://stackoverflow.com/questions/1980738/sql-delete-with-join-another-table-for-where-condition — Hituptony, Apr 28 '14 at 13:22
Disagree @Kermit :) I think its quite a helpful search term, so googling returned me nothing on the problem. If I've had this problem then somebody else will as well. — Jamie Hutber, Apr 28 '14 at 13:24
@JamieHutber You must be using Google wrong. I searched and it returned 102,000 results. — Kermit, Apr 28 '14 at 14:32
@JamieHutber you should restructure that sentence in a way that makes more sense. — Zane, Apr 28 '14 at 16:40
@Zane ... maybe they were going for this ... `WITH Documentation AS (SELECT * FROM [teh].[Googlez]) SELECT * FROM Documentation;` — swasheck, Apr 28 '14 at 16:46

score 2 · Accepted Answer · answered Apr 28 '14 at 13:20

2

DELETE s /* forgot to mention from which table you like to delete from these 2 */
FROM sex s
join users u on u.id = s.uid 
WHERE s.id = 195
and u.sessionCheck = 'd986a074c7549c566bfed1d4ad7ca491'

answered Apr 28 '14 at 13:20

juergen d

201,996
37
293
362

So hang on... what is this `DELETE S` this is deleting the entire row, `S` being the reference to `sex`? – Jamie Hutber Apr 28 '14 at 13:22
Yes, when joining to have to specify from which tables you delete. It is `delete table_to_delete_from from table_to_delete_from join ...` – juergen d Apr 28 '14 at 13:23
BTW You can specify more than one table and delete from multiple at once. – juergen d Apr 28 '14 at 13:25

score 0 · Answer 2 · answered Apr 28 '14 at 13:22

0

DELETE s.* FROM sex s,users u   WHERE u.id = s.uid AND s.id = 195 AND u.sessionCheck = 'd986a074c7549c566bfed1d4ad7ca491'

answered Apr 28 '14 at 13:22

Sajad Karuthedath

14,987
4
32
49

score 0 · Answer 3 · answered Apr 28 '14 at 13:23

DELETE FROM sex 
WHERE sex.id IN (SELECT id 
                 FROM users u
                 WHERE u.sessionCheck = 'd986a074c7549c566bfed1d4ad7ca491')

To delete from both tables

DELETE s.*,u.*
FROM sex s
inner join users u on u.id = s.uid 
WHERE s.id = 195
and u.sessionCheck = 'd986a074c7549c566bfed1d4ad7ca491'

mysql delete with cross table lookup

3 Answers3