MySql convert select query to update

Question

I have been struggling with a MySQL update query for some time, but cannot figure out the solution.

I have

Table_Items:
- id
- desc
- maintenance_times

and

Table_Maintenances:
- id
- Item_id
- maintenance_desc

They are related to each other like

Table_Items.id=Table_Maintenances.Item_id

Now i want to count number of maintenances from table Table_Maintenances for every item and update column maintenace_time in Table_Items.

I have a select query which works, but cannot figure out how to update every row (maintenance_times) in Table_items:

SELECT COUNT(b.Item_id) 
FROM `Table_Items` AS a
LEFT JOIN Table_maintenances as B ON a.id=b.Item_id 
GROUP BY b.Item_id

http://stackoverflow.com/questions/4268416/sql-update-with-sub-query-that-references-the-same-table-in-mysql — Sam, Dec 17 '13 at 17:12

score 2 · Accepted Answer · edited Dec 20 '13 at 08:38

2

Figured it out finally thanks to your example:

UPDATE 
  Table_Items m,
  (
    SELECT a.id, COUNT(b.Item_id) cnt 
    FROM `Table_Items` AS a
    LEFT JOIN Table_Maintenances as B on a.id=b.Item_id
    GROUP BY b.Item_id
  ) q
SET m.maintenance_times = q.cnt
WHERE m.id = q.id

edited Dec 20 '13 at 08:38

Thibault

1,566
15
22

answered Dec 18 '13 at 05:51

user3112031

39
7

MySql convert select query to update

1 Answers1