mySQL: how to update table and fields dependent on values in other tables?

Question

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 it makes me feel a little funny. I don't like to mess with data that is already right.]

Thanks for any help!

Answer 1

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)

Answer 2

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