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


Problem :

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!



Solution :

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)

Mysql Tutorials

Mysql Howto..

PHP/MYSQL - How are Database Level Constraints handled in Application

MySQL - How to group by on two fields and count

How to create a view/table in my context in mysql?

How to Insert / Update a Row in MySQL / PHP At The Same Time

How to load updated data from database(mysql) without refresh the page? [closed]

How to perform this MySQL query given this relation diagram

How to sort right to left language data in mysql?

How to generate next value in mysql table using java?

store how to large decimal numbers in mysql

how to find the total numbers of hours of 2 dates in mysql query

How to connect to MySQL database from Visual Basic 6

MySQL: How to get rows repeated x times

How should I design the database structure for this problem?

How to return all matches in mysql where value occurs in string at position n?

How to select rows with x but not y or z using HAVING in mysql

how to fetch only 80 rows from mysql database using Spring Data @query annotation?

Real big noobie here, how to use mysql so I can edit multiple pages easily?

How do I add a column to an existing FULLTEXT index?

how to get incremental backup for mysql using shellscript

(SQL) How to set column names of MySQL table as the delimited first line of my infile?

How do you drop an undo file from MySQL cluster?

How to handle game scores of last week in MySQL Table

How do I set a schema level default engine? [MySQL]

how to connect data base with EJB entity bean

How to calculate the day deffirence for same events

How to fetch column names from 'MySQL Create Table' Query string?

How to translate this sql to left join without subquery?

How to pass DataTable from one class to the other class

How does one convert seconds (or milliseconds) to a timestamp (or just a string that looks like a date) in mySql

WordPress not showing a category with ACF