MySQL, pull data from a column in a different table only if a second column matches

[luxsphinx]

Okay, so I've got two tables. One is called "orders_products" and the other is called "products". They contain different fields but have a couple that match up. When an order is placed, orders_products is filled with a bunch of data about the item in question - including a field called 'products_id' which is unique to the different items. The "products" table has less information about orders (no quantity field or tax amounts), but more information about the items.

What I have done is added two new fields into "orders_products" that need to contain a part number and an old part number (called product_part & product_old_part respectively). Going forward, all new orders will fill in these fields, but what I can't figure out is how to fill in the fields for thousands of previous orders.

Somehow I need to tell MySQL to look at the "orders_products" table and find the 'products_id' field. Then find a match for that number in the 'products_id' field of the "products" table. When it finds a match, it needs to take the values listed for 'product_part' and 'product_old_part' from the "products" table and insert it into the corresponding fields in the "orders_products" table.

Any thoughts on how to accomplish this? Seems like it should be easy, but that I'm missing the obvious. Any help would be appreciated. Thanks

Here's what the tables kind of look like:

orders_products

--------------------------------------------------------
orders_id| products_id| orders_tax| products_part| etc |
--------------------------------------------------------
   1           12         0.34          NULL       ...
   2           13         0.64          NULL       ...
   3           14         3.52          NULL       ...
   4           15         9.76          NULL       ...
  ...          ...        ...           ...        ...
--------------------------------------------------------
 

products

--------------------------------------------------------
products_id |   products_model |   products_part | etc |
--------------------------------------------------------
   12               IB45              47592N24     ...
   13               SN59              08572E84     ...
   14               OI18              84083R09     ...
   15               PE97              08647W38     ...
  ...               ...               ...          ...
--------------------------------------------------------
 

EDIT: There was a duplicate 12, sorry about that.

[dgreenhouse]

I noticed that you have a field named products_model in the products table and that there are 2 rows with products_id = 12. This; I assume, could add an additional complexity.

I haven't tested this, but this might be a start.

Also, if your tables are InnoDB you might have to use the "on update" clause.

Finally, I think you might want to consider normalizing your database schema.
If I'm correct, you'll paint yourself into a corner as things progress.

UPDATE orders_products,products SET orders_products.products_part = products.products_part,
orders_products.old_products_part = products.old_products_part
WHERE orders_products.products_id = products.products_id and {how do you differentiate products_model} and isnull(orders_products.products_part)
 

Just tested and it works; except for the the products_model issue I mentioned.

[luxsphinx]

UPDATE orders_products,products SET orders_products.products_part = products.products_part,
orders_products.products_old_part = products.products_old_part
WHERE orders_products.products_id = products.products_id and isnull(orders_products.products_part)
 

This seems to have worked perfectly, thank you very much dgreenhouse!