Trying to cleanup table.. Stuck.. Help..

[DivaVocals]

I'm using this script to locate records in my SBA table where the product no longer exists..

SELECT * 
FROM   zen_products_with_attributes_stock
LEFT OUTER JOIN zen_products
  ON (zen_products.products_id = zen_products_with_attributes_stock.products_id)
  WHERE zen_products.products_id IS NULL

I have validated that these products no longer exist.. so I'm confident that these records can be deleted from the SBA table.. I need to know how to delete these records.. Kinda stumped on how to do this.. Hoping smarter brains than mine can help here..

[mc12345678]

I'm using this script to locate records in my SBA table where the product no longer exists..

SELECT * 
FROM   zen_products_with_attributes_stock
LEFT OUTER JOIN zen_products
  ON (zen_products.products_id = zen_products_with_attributes_stock.products_id)
  WHERE zen_products.products_id IS NULL

I have validated that these products no longer exist.. so I'm confident that these records can be deleted from the SBA table.. I need to know how to delete these records.. Kinda stumped on how to do this.. Hoping smarter brains than mine can help here..

This work?

delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id = null;

Left off the prefix of your table(s), but....

[mc12345678]

I'm using this script to locate records in my SBA table where the product no longer exists..

SELECT * 
FROM   zen_products_with_attributes_stock
LEFT OUTER JOIN zen_products
  ON (zen_products.products_id = zen_products_with_attributes_stock.products_id)
  WHERE zen_products.products_id IS NULL

I have validated that these products no longer exist.. so I'm confident that these records can be deleted from the SBA table.. I need to know how to delete these records.. Kinda stumped on how to do this.. Hoping smarter brains than mine can help here..

Or using your specific results of your query.

Delete from products_with_attributes_stock pwas
Where pwas.products_id in (
SELECT UNIQUE pwas1.products_id 
FROM   products_with_attributes_stock pwas1
LEFT OUTER JOIN products p
  ON (p.products_id = pwas1.products_id)
  WHERE p.products_id IS NULL
)

Though I think this later version is a bit more consuming than the earlier... Ideally left inner joins should be performed rather than outer joins... The first version does that (and ought to provide you the same results as what you posted), this second uses your outer join method. I threw in the unique statement just to be sure, probably doesn't need it.

[DivaVocals]

This work?

delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id = null;

Left off the prefix of your table(s), but....

Nope.. here's the result..

1 queries executed, 1 success, 0 errors, 0 warnings

Query: delete pwas from zen_products_with_attributes_stock pwas left join zen_products p on p.products_id = pwas.products_id where p.pr...

0 row(s) affected

Execution Time : 0.094 sec
Transfer Time : 0.001 sec
Total Time : 0.096 sec

[DivaVocals]

Or using your specific results of your query.

Delete from products_with_attributes_stock pwas
Where pwas.products_id in (
SELECT UNIQUE pwas1.products_id 
FROM   products_with_attributes_stock pwas1
LEFT OUTER JOIN products p
  ON (p.products_id = pwas1.products_id)
  WHERE p.products_id IS NULL
)

Though I think this later version is a bit more consuming than the earlier... Ideally left inner joins should be performed rather than outer joins... The first version does that (and ought to provide you the same results as what you posted), this second uses your outer join method. I threw in the unique statement just to be sure, probably doesn't need it.

and this doesn't work either..

1 queries executed, 0 success, 1 errors, 0 warnings

Query: Delete from zen_products_with_attributes_stock pwas Where pwas.products_id in ( SELECT UNIQUE pwas1.products_id FROM zen_product...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pwas
Where pwas.products_id in (
SELECT UNIQUE pwas1.products_id
FROM zen_pro' at line 1

Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0.664 sec

[mc12345678]

This work?

delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id = null;

Left off the prefix of your table(s), but....

Oops...

Used equals symbol instead of is assignment:

delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id IS null;

[Ajeh]

What comes up when you run this?

SELECT * 
FROM zen_products_with_attributes_stock 
WHERE zen_products_with_attributes_stock.products_id NOT IN (SELECT zen_products.products_id FROM zen_products);

[mc12345678]

What comes up when you run this?

SELECT * 
FROM zen_products_with_attributes_stock 
WHERE zen_products_with_attributes_stock.products_id NOT IN (SELECT zen_products.products_id FROM zen_products);

I came up with the same results as the previous select statement(s) (ie. in the sample I've created, two entries for products_id each with different data, both entries are returned and can also be deleted). Have seen that there are a number of ways to analyze/obtain the desired result(s)

Not exists is another form..

select *
FROM zen_products_with_attributes_stock
Where not exists (Select Null from zen_products
where zen_products.products_id = zen_products_with_attributes_stock.products_id);

[DivaVocals]

Oops...

Used equals symbol instead of is assignment:

delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id IS null;

BAM!! That did it.. Makes perfect sense why it failed the first time.. I MIGHT have spotted the = vs IS too, but I was caffeine deprived and cranky..

Thanks!!

[mc12345678]

BAM!! That did it.. Makes perfect sense why it failed the first time.. I MIGHT have spotted the = vs IS too, but I was caffeine deprived and cranky..

Thanks!!

I'll just say that I wasn't fully engaged either... Gave it a shot without testing..

If you haven't considered it further, (obviously) Ajeh's solution looks cleaner as it uses a bit more of sensible lexicon: Hey here's a list of things on the right, now on the left show me the things in the left that are not in the right...

I don't know the details of when this action is to be performed (deletion of "extra" product entries) (as in multiple times in execution) or how many such entries are to be removed, or the overall load on the system for any of the above three functional methods, but, if infrequently run, whatever works and accomplishes the desired task. :)