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 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)
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]