开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜