CODEWITHSUNDEEP
mysql
Dizzy49
Dizzy49

Reputation: 1530

Why won't this DELETE query work?

I can't figure out why this query won't work. I did a SELECT to verify it's pulling the rows that I want. I put in DELETE and now it won't work.

Here is my SELECT query:

SELECT dh.dragon_list_id, dh.habitat_list_id 
FROM dragon_to_habitat dh, dragon_list dl
  WHERE dh.dragon_list_id = dl.dragon_list_id
    AND dl.user_id = 1
    AND dl.is_deleted = false

I'm sure it's something stupid I'm overlooking. Little help is appreciated.

DELETE FROM dragon_to_habitat dh
  WHERE dh.dragon_list_id = dragon_list.dragon_list_id
    AND dragon_list.user_id = 1
    AND dragon_list.is_deleted = false

I also tried this query with a JOIN that did not work:

DELETE 
FROM dragon_to_habitat as dh JOIN 
     dragon_list as dl ON dh.dragon_list_id = dl.dragon_list_id
WHERE dl.user_id = 1 AND dl.is_deleted = false

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 'as dh JOIN dragon_list as dl ON dh.dragon_list_id = dl.dragon_list_id WH' at line 2

I only want to clear the values in the dragon_to_habitat table. The dragon_list table should be untouched.

For good measure, here's my schema: http://sqlfiddle.com/#!2/7ac6a1

Upvotes: 0

Views: 111

Answers (3)

Anil Saini
Anil Saini

Reputation: 637

You need to JOINthe dragon_to_habitatand dragon_list table -

DELETE FROM dragon_to_habitat AS dh
JOIN dragon_list AS dragon_list ON dh.dragon_list_id = dragon_list.dragon_list_id
WHERE dragon_list.user_id = 1 AND dragon_list.is_deleted = false

Upvotes: 2

Shriprasad
Shriprasad

Reputation: 61

DELETE FROM 
 dragon_to_habitat 
USING 
  dragon_to_habitat dh 
INNER JOIN 
  dragon_list dl 
ON 
  dh.dragon_list_id = dl.dragon_list_id 
AND 
  dl.user_id = 1 
AND 
  dl.is_deleted = false

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18767

Try using JOIN:

DELETE 
FROM dragon_to_habitat as dh JOIN 
     dragon_list as dl ON dh.dragon_list_id = dl.id
WHERE dl.user_id = 1 AND dl.is_deleted = false

Upvotes: 0

Related Questions