Koozali.org: home of the SME Server

Contribs.org Forums => General Discussion => Topic started by: kruhm on April 23, 2007, 05:35:52 PM

Title: any mysql experts out there?
Post by: kruhm on April 23, 2007, 05:35:52 PM

I have a table of products:
book1
book2...
book10

I get product updates (new/changed) from an outside source.
book11
book12

Along with the updates, I get a list of current products.
book2
book3
book5
book11
book12

Any product not on the list is no longer valid. How do I delete the records that aren't on the list of current products?

Any help or pointing in the right direction is greatly appreciated.

Title: any mysql experts out there?
Post by: Curly on April 23, 2007, 08:49:37 PM

I'm no mysql expert, but I wll have a go

something like:

Code: [Select]

delete products where name not in (select name from current products)

but I'm not sure if mysql supports these sub-queries

Title: any mysql experts out there?
Post by: kruhm on April 23, 2007, 11:40:24 PM

That was it. Thank you very much.

I was hoping to delete the rows straight from the TXT file but appearently that's not possible.

For anyone else:

I had to load the list into a second table:

Code: [Select]


mysql -e "LOAD DATA LOCAL INFILE 'products.txt' INTO TABLE PRODUCTS_CURRENT LINES TERMINATED BY '\r\n';" databasename

Then delete the products not on the reference file:

Code: [Select]


mysql -e "delete from PRODUCTS where not exists (select 1 from PRODUCTS_CURRENT where PRODUCTS.product_id = PRODUCTS_CURRENT.product_id);" databasename