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 constantly hinky so let me run some good sql on it and it Must be able to clean. This is the story:

  table 'uc_products': nid Model 1 asdf 2 qwer 3 ghjk 'uc_product_stock' table: nid sku 1 asdf 22 qwer 34 ghjk  

You can see that model = sku, and nid = nid in table uc_products all have correct data, and uc_product_stock is with bad data (nid that sku does not match). I want to see through uc_product_stock and each entry in comparison to uc_product:

  • Model == sku & amp; Nid == Nid: Yes! Data is good!
  • Model == sku & amp; Needless! = Nidh: Boo! Bad data, therefore uc_product.nid should be updated to match the value in uc_product.nid

[I think the method of another cruel force to do this is SKU / The model will match each row and reset each uc_product_stock.nid to match uc_product.nid - The principal is that the wrong people will be corrected and the correct person will be reset to the same value and be right. I am open to it if you think this is the best answer, but it seems a little fun to me. I do not like to mess with the correct data in advance.]

Thanks for any help!

Can you just run a simple update statement like this:

 < Code> UPDATE uc_product_stock a SET a.nid = (SELECT MIN (b.nid) uc_products b WHERE b.sku = A.sku)  

Comments

Popular posts from this blog

c# - sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible' -

Calling GetGUIThreadInfo from Outlook VBA -

Obfuscating Python code? -