SQL QUERY (DELETE BETWEEN TWO TABLES)

Question

I need a query for PHP or an idea for this.

I have two tables:

News

ID MAME
--------
 1 Test
 2 Test1
 3 Test2
 4 Test3
 7 Test3

Products

ID NAME PRICE
-------------
 1 Test 11
 9 Test2 22
 8 Test4 122

I need to delete records from Products where ID doesn't exist in News.

Can someone help me with some ideas?

Thanks for your time!

Do a RIGHT JOIN on the `News` table from the `Products` table, and check for `News.ID = Null`. See also http://stackoverflow.com/questions/2973558/select-a-value-where-it-doesnt-exist-in-another-table — Robert Harvey, Jul 06 '11 at 00:16

score 3 · Answer 1 · answered Jul 06 '11 at 00:19

3

Try SELECT * FROM PRODUCTS WHERE ID NOT IN (SELECT ID FROM NEWS)

If this works then change SELECT * to DELETE It's good practice to try a select to make sure you are getting the right data before you delete.

answered Jul 06 '11 at 00:19

Paul.s

38,494
5
70
88

score 1 · Answer 2 · edited Jul 06 '11 at 01:20

1

DELETE Products
FROM Products
  LEFT JOIN News
    USING (ID)
WHERE News.ID IS NULL;

If you want to check what gets deleted from products, use Paul's excellent suggestion of using a select first.

So check with

SELECT * 
FROM Products
  LEFT JOIN News
    USING (ID) 
WHERE News.ID IS NULL;

and switch back to

DELETE Products
FROM Products
  LEFT JOIN News
    USING (ID)
WHERE News.ID IS NULL;

if you are happy with the result. It's IMPORTANT you DELETE Products FROM rather than DELETE FROM , otherwise will will delete from both tables.

edited Jul 06 '11 at 01:20

ypercubeᵀᴹ

113,259
19
174
235

answered Jul 06 '11 at 00:18

JustDanyul

13,813
7
53
71

He wants to delete FROM Products, not News. Will this still work? – Robert Harvey Jul 06 '11 at 00:19
1

Yes, DELETE Products FROM [table_reference] means it will only delete the rows from the products table. Basically, all rows from products which are in the set of results from the join will be deleted, nothing from news will be deleted. – JustDanyul Jul 06 '11 at 00:25
1

http://dev.mysql.com/doc/refman/5.0/en/delete.html , look at the examples of doing joins in DELETE statements :) Do a find for "For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. " on the page :) – JustDanyul Jul 06 '11 at 00:28
Typically you would have to alias the Products table – Joe Phillips Jul 06 '11 at 00:40
whys that? they have different names? This is straight from the mysql documentation: DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id; No aliases? – JustDanyul Jul 06 '11 at 00:42
I guess I'm wrong. I don't write much mysql and I'm pretty sure you have to alias in t-sql – Joe Phillips Jul 06 '11 at 00:45
aah, your using a "real" database ;) its been way too many moons ago since i was near a Tsql based RDBMS for me to distribute that :P – JustDanyul Jul 06 '11 at 00:49

SQL QUERY (DELETE BETWEEN TWO TABLES)

2 Answers2