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 stackoverflow.com/questions/2973558/… — Robert Harvey
– Robert Harvey, Commented Jul 6, 2011 at 0:16

Paul.s · Accepted Answer · 2011-07-06 00:19:32Z

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 6, 2011 at 0:19

Paul.s

38.8k5 gold badges73 silver badges88 bronze badges

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

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-07-06 01:20:53Z

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 6, 2011 at 1:20

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

answered Jul 6, 2011 at 0:18

Daniel1979

14.1k7 gold badges59 silver badges72 bronze badges

7 Comments

Robert Harvey Over a year ago

He wants to delete FROM Products, not News. Will this still work?

Daniel1979 Over a year ago

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.

Daniel1979 Over a year ago

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

Joe Phillips Over a year ago

Typically you would have to alias the Products table

Daniel1979 Over a year ago

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?

|

Collectives™ on Stack Overflow

SQL QUERY (DELETE BETWEEN TWO TABLES)

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related