0

I'm usually pretty resourceful, but I'm stuck on this one. Any help would be appreciated.

Say I've got a table for produce, like this, including counts of sold/in stock for each produce type.

+--------------+--------------+------+-----+
| Field        | Type         | Null | Key | 
+--------------+--------------+------+-----+
| produce_type | varchar(100) | NO   | PRI |
| sold_count   | int(8)       | YES  |     | 
| stock_count  | int(8)       | YES  |     |
+--------------+--------------+------+-----+

I'm doing a separate insert using external data for each of the 'stock' and 'sold' counts, with hundreds to thousands of produce_types at a time. I may have data with a given produce_type existing only in the 'stock' or 'sold' data to be inserted, but want all to be present in the table.

So, e.g., doing one insert for sold_count ('potato', 3), ('onion', 5) and one for stock_count ('potato', 8), ('carrots', 6), I'd want to end up with this:

+--------------+------------+-------------+
| produce_type | sold_count | stock_count |
+--------------+------------+-------------+
| potato       |          3 |           8 |
| onion        |          5 |        NULL |
| carrots      |       NULL |           6 |
+--------------+------------+-------------+

So I'd need to join to existing data upon the second column's insert statement, but all I see here or elsewhere on the web is instructions for joins when inserting from another table.

INSERT IGNORE doesn't do it, as one of the 'potato' columns wouldn't get written to.

INSERT ... ON DUPLICATE KEY UPDATE gets closer but I can't figure out how to set the update field to the value from the dataset I'm inserting.

Do I need to create a temp table for the 2nd insert (+ outer join)? Any structurally simpler way of doing this?

Thanks in advance.

Edit: I think I can probaly use this: https://stackoverflow.com/a/3466/2540707

Community
  • 1
  • 1
evan_b
  • 1,079
  • 1
  • 11
  • 22

1 Answers1

0

Does this work?

insert into produce ( produce_type, sold_count )
            select    produce_type, sold_count from sold_data
            on duplicate key update sold_count = ( select sold_count from sold_data
                                                   where produce.produce_type = sold_data.produce_type
                                                   );
user3666197
  • 1
  • 6
  • 50
  • 92
Matthias Grabiak
  • 1
  • Not quite. The first insert will create the produce_type and sold_count, then the later insert updates the stock_count based on external data. – evan_b Sep 25 '14 at 18:22