icosac icosac - 16 days ago 7
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,
CONSTRAINT FOREIGN KEY (sin) REFERENCES person(sin)
)


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
WHERE 2 < (SELECT COUNT(B.sin)
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

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;