icosac icosac - 1 year ago 80
MySQL Question

MySql using subquery with update

I've got this table patient and have to query it for

Update the database in order to provide a 3% discount to all patients in a room that has more than 2 patients.

The table is made as follow:

CREATE TABLE patient (
sin varchar(20) NOT NULL,
disease varchar (20),
bed varchar (20),
room varchar (20),
hospital varchar (0),
fee varchar(20),
entry_date date NOT NULL,
exit_date date,

So I thought to find all the patients' rooms that presents more then 2 patients, and then update the table:

UPDATE patient C
INNER JOIN patient D ON C.sin=D.sin and D.sin IN (SELECT A.sin
FROM patient A
FROM patient B
WHERE A.hospital=B.hospital and A.room=B.room and A.exit_date IS NULL and B.exit_date IS NULL)
SET C.fee=C.fee*0.97

The problem is that I'm getting the error:

You can't specify target table 'C' for update in FROM clause

Is there a way to use subquery with update?
Thank you very much.

Answer Source

You are on the right track. But you want to join on room information, not patient information. So:

UPDATE patient p JOIN
       (select hospital, room
        from patient
        where exit_date is null
        group hospital, room
        having count(*) > 2
       ) r
       ON p.hospital = r.hospital and p.room = r.room
    SET p.fee = p.fee * (1 - 0.03)
    WHERE exit_date is null;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download