CODEWITHSUNDEEP
mysqlsql
Siegfried.V
Siegfried.V

Reputation: 1595

Update table depending on other table's value

I have 2 tables that I will call GROUPITEMS, and ITEMS, that are as the following :

GROUPITEMS

ID  |  PRIORITY_MIN  |  PRIORITY_MAX

ITEMS

ID  |  ID_GROUP  |  PRIORITY

What I am looking for is a request updating GroupItems "PRIORITY_MIN" and "PRIORITY_MAX" checking each item's priority inside, something like that :

UPDATE GROUPITEMS SET PRIORITY_MIN = MIN (ITEMS.PRIORITY WHERE ID_GROUP = GROUPITEMS.ID),
PRIORITY_MAX = MAX (ITEMS.PRIORITY WHERE ID_GROUP = GROUPITEMS.ID) WHERE GROUPITEMS.ID=myGroupId;

Is there a possibility to do such a thing in MySQL? Hope my explaination was clear enough.

Upvotes: 0

Views: 44

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You are almost there. You can do it with subquery as below:

UPDATE GROUPITEMS 
SET PRIORITY_MIN = (select min(PRIORITY) from ITEMS WHERE ID_GROUP = GROUPITEMS.ID),
PRIORITY_MAX = (select max(PRIORITY) from ITEMS WHERE ID_GROUP = GROUPITEMS.ID);

using join: You can also update any table by joining it with other tables. In this scenario I have joined table groupitems with a sub query which is calculating max and min priority from items table for each group.

select id_group,min(priority) minpriority ,max(priority)maxpriority from  items
    group by id_group

Then through joining I have selected minpriority and maxpriority from this result set to update priority_min and priority_max of groupitems table by joining them with t.id_group=groupitems.id condition.

update groupitems
inner join 
(select id_group,min(priority) minpriority ,max(priority)maxpriority from  items
group by id_group)t
on t.id_group=groupitems.id
set priority_min= t.minpriority , priority_max=t.maxpriority;

Upvotes: 1

Related Questions