Update a column of multiple rows of a table using joins

Question

EDIT

I tried this query but got an error:

UPDATE consultation_med
SET consultation_med.given_quantity = 0
FROM
consultation_med
left join consultation on consultation.consultation_id = consultation_med.consultation_id
left join visit on visit.visit_id = consultation.visit_id
 WHERE visit.visit_id='191'
           AND consultation_med.given_quantity = '361'

the error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM consultation_med left join consultation on consultation.consultation_id = c' at line 3

END EDIT

I need to update a table called consultation_med and change the given_quantity of a medication into 0 when the status of a visit visit_status is changed from Active to Inactive.

Please take note that in the visit table, each visit_id can have multiple consultation_id in the next table which is consultation. And in each consultation_id doctors can give multiple medication with different quantities so we have now for each consultation_id multiple rows in consultation_med table.

Here are the tables:

So let's take the visit_id=173 that already had 4 consultation_id and in each consultation doctors prescribed 1 medication. So we have 4 rows related to visit where visit_id=173 in consultation_med table. The data entry had an error and he need to send an request to the admin by changing the status of this visit to Inactive, so every medication quantity given should be back to inital storage, thus we should change the given_quantity of each 4 rows to 0.

I tried in PHP the following query but apparently it is taking just one row and updating it:

$med_pharmacy_id = $res['med_pharmacy_id'];
$consultation_med_id = $res['consultation_med_id'];

$update = "UPDATE consultation_med 
SET given_quantity = 0 
WHERE med_pharmacy_id = :mid
AND clinic_id = :cid 
AND consultation_med_id = :cmid";

Then I tried to use left JOIN inside update query:

UPDATE consultation_med 
left join consultation on consultation.consultation_id = consultation_med.consultation_id
left join visit on visit.visit_id = consultation.visit_id

SET consultation_med.given_quantity = 0 
WHERE visit.visit_id='173'
AND consultation_med.given_quantity = '361'

The query is still wrong plus an extra error which is:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Martin Volek · Answer 1 · 2017-07-20T09:35:45.740

3

That error "1175" you need to change in edit or setting -> preferences in your SQL editor to DISABLE SAFE MODE, or you should to use phpmyadmin.

edited Jul 20 '17 at 09:35

answered Jul 20 '17 at 09:34

Martin Volek

315
2
13

I need to keep it, so later on user laptop can be done normally so I need to change the query – alim1990 Jul 20 '17 at 09:35
hmmm, you should also try subquery, thats good for updates, but little harder – Martin Volek Jul 20 '17 at 09:49
example of code is here -> https://stackoverflow.com/questions/45191560/sql-subquery-return-more-than-1-row – Martin Volek Jan 19 '18 at 09:04

score 2 · Answer 2 · answered Jul 20 '17 at 10:21

In MySQL, you should write the update statement as: update-join-set-where

Something like this:

UPDATE consultation_med
LEFT JOIN consultation on consultation.consultation_id = consultation_med.consultation_id
LEFT JOIN visit on visit.visit_id = consultation.visit_id
SET consultation_med.given_quantity = 0
 WHERE visit.visit_id='191'
           AND consultation_med.given_quantity = '361'

For more details, visit this link

Update a column of multiple rows of a table using joins

2 Answers2