MySQL Forums
Forum List  »  Newbie

SQL Update Table Based on Join and Multiple Where Clauses
Posted by: Tech Girl
Date: December 09, 2021 02:24AM

I am trying to retrieve appointments from clients whose status is 1 and update it to 0. This is my select statement :

select *
from tbl_appointment
left join tbl_client ON tbl_client.id = tbl_appointment.client_id
WHERE tbl_client.mfl_code = '10808'
AND tbl_appointment.app_status = 'LTFU'
AND tbl_appointment.active_app = 1;

I get the correct results. I am trying to update the appointment table, specifically the active_app column based on this select statement, but i cant seem to get the query right.
My two attempts look like this:

This yields a syntax error ```left join tbl_client on (tbl_client.id = tbl_appointment.c' at line 2```

UPDATE tbl_appointment
SET active_app = 0
from tbl_appointment
left join tbl_client on tbl_client.id = tbl_appointment.client_id
WHERE (tbl_client.mfl_code = '10808'
AND tbl_appointment.app_status = 'LTFU'
AND tbl_appointment.active_app = 1)

And this updates everything
```
update tbl_appointment
left join tbl_client
on (tbl_client.id = tbl_appointment.client_id
AND tbl_client.mfl_code = '10808'
AND tbl_appointment.app_status = 'LTFU'
AND tbl_appointment.active_app = 1)
set tbl_appointment.active_app = 0 ;
```
And this :
```
START TRANSACTION;

SELECT mfl_code, clinic_number FROM tbl_client WHERE mfl_code = '13087' FOR UPDATE;

UPDATE tbl_appointment SET active_app = '1'
WHERE active_app = '0' and app_status = 'Notified';

COMMIT
```

Any pointers to how to update based on joint tables will be appreciated. Thanks.

Options: ReplyQuote


Subject
Written By
Posted
SQL Update Table Based on Join and Multiple Where Clauses
December 09, 2021 02:24AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.