CODEWITHSUNDEEP
mysqlsqljoin
YannickHelmut
YannickHelmut

Reputation: 555

SELECT, JOIN and UPDATE at the same time

As more and more people do, I will use an sms api to send some people a reminder of their appointment today.

So I'm getting the data without any problems using this query:

SELECT users.id as userid, consultations.patient_id as patientid, consultations.title as patientname, patients.mphone as phone
FROM users
JOIN consultations
ON users.id = consultations.user_id
JOIN patients
ON consultations.patient_id = patients.id
WHERE users.sms > 0 AND DATE(consultations.start) = (CURDATE() + INTERVAL 1 DAY) AND patients.mphone <> ''

At the same time I'd like to update the consultations table and set consultations.reminder = true and substract 1 from users.sms for each row that is output for each users.id... Can I do that ?

My first problem is that if I add a count(), the query gets automatically grouped (or just the last line is shown, not sure why).

If you have a suggestion... :)

Upvotes: 0

Views: 137

Answers (1)

William
William

Reputation: 6610

My MySQL is a little bit rusty but you could insert your results into a temp table, and then update the other tables from there. Other than that, a select statement is not capable of modify data.

Try something like this;

CREATE TEMPORARY TABLE IF NOT EXISTS TempAppointments AS (
SELECT users.id as userid, consultations.patient_id as patientid, consultations.title as patientname, patients.mphone as phone
FROM users
JOIN consultations
ON users.id = consultations.user_id
JOIN patients
ON consultations.patient_id = patients.id
WHERE users.sms > 0 AND DATE(consultations.start) = (CURDATE() + INTERVAL 1 DAY) AND patients.mphone <> '')


UPDATE C
SET reminder = true
FROM consultations AS C INNER JOIN TempAppointments AS T ON C.user_id = T.user_id

UPDATE U
SET sms = (sms - 1)
FROM Users AS U INNER JOIN TempAppointments AS T ON U.user_id = T.user_id

Remember to drop your temp table at the end though;

DROP TEMPORARY TABLE TempAppointments

Upvotes: 1

Related Questions