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

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..

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..
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....

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..

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.

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....

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

mc12345678:

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 [B]IS[/B] null;

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

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

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

mc12345678:

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 [B]IS[/B] 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:laugh:.. 

Thanks!!

DivaVocals:

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:laugh:..

Thanks!!

I'll just say that I wasn't fully engaged either... :cheers: 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. :)

mc12345678:

I'll just say that I wasn't fully engaged either... :cheers: 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. :)

Well thanks for the help.. I can now move forward with the SBA conversion I'm working on..

Now this brings to light a VERY big gap in SBA.. There appears to be a lack of data integrity checks.. There should be some kind of check so that removing products SHOULD also remove them from the SBA table.. Same is true of attributes.. This same site I had products in the BA table with attribute IDS that no longer existed..

mc12345678:

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:**
>
> and this doesn't work either..

> **DivaVocals:**
>
> Well thanks for the help.. I can now move forward with the SBA conversion I'm working on..
> 
> Now this brings to light a VERY big gap in SBA.. There appears to be a lack of data integrity checks.. There should be some kind of check so that removing products SHOULD also remove them from the SBA table.. Same is true of attributes.. This same site I had products in the BA table with attribute IDS that no longer existed..

Already taken care of in the branch mc12345678_sba154 of the version for ZC 1.5.3/1.5.4.

Both cases actually. So, at least people that install it anew will have all the benefits; however, those that have used the old version(s) may need to do what you have done, though may also include it into the "setup"/conversion.