CODEWITHSUNDEEP
mysqlsql
Nikitas
Nikitas

Reputation: 1003

Mysql DELETE with SELECT(?)

I have 2 tables and i am a little confused...

      |products|                          
________________________
|  id  | products_name |
|______________________|
|   1  |  iphone4      |
|   2  |  htc desire   |
    etc.............


     |special_price|
________________________
|  id  | products_price|
|______________________|
|   1  |  400          |
|   2  |  500          |
    etc.............

All i want to do is:

DELETE (ALL) FROM special_price WHERE products_name='iphone4'

How can this happen?

Upvotes: 0

Views: 130

Answers (2)

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230551

Try this:

DELETE sp FROM special_price sp
INNER JOIN products p ON p.id = sp.id
WHERE p.products_name = "iphone4";

Upvotes: 4

rekans
rekans

Reputation: 341

You need to find the id(s) for that product_name, try this:

with IN: (useful for complex conditions)

DELETE FROM special_price 
WHERE id IN ( SELECT id FROM products WHERE products_name = 'iphone4' )

Upvotes: 4

Related Questions