mySQL: how to update table and fields dependent on values in other tables?
I have a database with hinky data in it. Fortunately it is consistently hinky so I should be able to run some nice SQL on it and clean it up. Here's the story:
Table 'uc_products':
nid model
1 asdf
2 qwer
3 ghjk
Table 'uc_product_stock':
nid sku
1 asdf
22 qwer
34 ghjk
So you can see that model=sku, and nid=nid. The table uc_products has all the correct data in it, and uc_product_stock is the one with bad data (nid's that don't match the sku's). I want to look through uc_product_stock and for each entry, compare to uc_product:
- Where model==sku & nid==nid : yeah! Data is good!
- Where model==sku & nid!=nid : boo! Bad data, so uc_stock_product.nid should be updated to match the value in uc_product.nid
[I guess a more brute force way to do this would be to match each row on SKU/model and reset each uc_product_stock.nid to match the uc_product.nid -- the principal being that the wrong ones would be corrected and the right ones would be reset to the same value and stay right. I am open to that if you think it's the best answer, but开发者_StackOverflow社区 it makes me feel a little funny. I don't like to mess with data that is already right.]
Thanks for any help!
Can you just run a simple update statement like this:
UPDATE uc_product_stock a
SET a.nid = (SELECT MIN(b.nid)
FROM uc_products b
WHERE b.sku = a.sku)
I haven't tested this but this should work:
UPDATE uc_product_stock AS s
INNER JOIN uc_product AS p ON p.model=s.sku SET s.sku=p.model
WHERE p.nid!=s.nid AND p.model=s.sku
精彩评论