Solved

Query to remove lines from 1 table by referencing another table

Posted on 2011-02-13
2
282 Views
Last Modified: 2012-06-21
hi guys

I  have 2 tables referenced within a Mysql Database
 
Table 1 has arround 3000 lines of data and Table 2 has 1300 lines of data. both tables have different fields but both have a field named "product" which is a numeric number.

I am after some how querying the table with 1300 lines which has a field named product. And instructing Table 1 to delete any lines that DO NOT reference those that are within Table 2. Hereby me having 2 tables with 1300 lines.

The reason behind this is that Table 1 is a product table that has an entire product database but 1700 of them are old and not used. Table 2 has the stock control for the products and i only want to show the stock that has products on a website instead of people seeing products which are out of stock

Thank you in advance
0
Comment
Question by:TG-Steve
2 Comments
 
LVL 9

Accepted Solution

by:
AriMc earned 250 total points
ID: 34883149
delete from table1 where product not in (select product from table2)
0
 

Author Closing Comment

by:TG-Steve
ID: 34883189
worked like a charm thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now