Update a column in a table only if the condition for the number of values for a field in another table is met
I am trying to figure out how to achieve the following. (This is a simplified version that ignores / removes unneeded columns.)
I have a table Item with columns
item_id product
I have another table Order with columns
order_id product item_id
Here is a description of the problem.
For a given item_id in the Item table, find the orders that are associated with it with only one product in the Order table.
Keep in mind, one single product can mean one or more rows for a unique combination of product and item_id . In this case, I need to take a product from the Order table and update the Item table with this value.
So, if the Order table matters
Scenario I
Order Table
order_id product item_id
1 apple 12
2 apple 12
Then I need to update the product in the Item table with "apple"
Scenario II
However, if the Order table matters
order_id product item_id
1 apple 12
2 orange 12
nothing needs to be done. The product in the item does not need updating. (By default this is null
.). This is a one-time data correction. The interaction between the Order and Item table will be handled by the application. In any case, the product in the Order table remains after the conditional update in the Item table.
What is the solution to this problem?
source to share
What seems to be asking is:
UPDATE item_table i
SET i.product =
(SELECT
MIN(o.product)
FROM order_table o
WHERE i.item_id = o.item_id
GROUP BY o.item_id
HAVING COUNT(DISTINCT o.product) = 1
)
WHERE i.product IS NULL
;
See it in action: SQL Fiddle .
Please comment if this requires customization / further information.
source to share
update Items i
set i.product = (select max(o.product) from Orders o where o.item_id = i.item_id)
where 1 = (
select count(distinct o.product)
from Orders o
where o.item_id = i.item_id
)
Note. I think some of the confusion with your post has to do with the use of the word unique
. I think you meant to use the word instead distinct
.
So when you say:
For a given item_id in the Item table, look for commands that have only one unique product associated with it in the Order table.
I think you meant to say:
For a given item_id, in the Item table, look for commands that have only one distinct product associated with it in the Order table.
source to share