Mysql DELETE with SELECT(?)

Question

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?

Why don't you just DELETE FROM special_price WHERE products_name='iphone4' ? — Li0liQ
– Li0liQ, Commented Jan 5, 2012 at 12:05

rekans · Accepted Answer · 2012-01-05 12:15:59Z

4

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' )

edited Jan 5, 2012 at 12:15

answered Jan 5, 2012 at 12:07

rekans

3413 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergio Tulentsev · Accepted Answer · 2012-01-05 12:48:27Z

4

Try this:

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

edited Jan 5, 2012 at 12:48

answered Jan 5, 2012 at 12:05

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

3 Comments

newtover Over a year ago

this will delete from sp table only

Sergio Tulentsev Over a year ago

Yes, correct. That's what I meant. OP probably doesn't want to delete the product itself, just the special offer on it.

ypercubeᵀᴹ Over a year ago

I think you meant to write: DELETE sp FROM ... so rows only from table special_price are deleted.

Collectives™ on Stack Overflow

Mysql DELETE with SELECT(?)

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related