MySql using subquery with update

Question

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.

Gordon Linoff · Accepted Answer · 2016-11-25 17:12:08Z

1

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 by 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;

edited Nov 25, 2016 at 17:12

answered Nov 24, 2016 at 20:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

icosac Over a year ago

Worked perfectly, just group group by hospital, room and not group hospital, room. If you can modify your answer so it's perfect Thank you very much!!

icosac · Accepted Answer · 2016-11-24 20:41:00Z

0

Even if @Gordon Linoff gave the right answer, and probably the best, I got an idea just after asking the question. From the moment that I need to have at least 3 people in the same room, I'll check if that's the case, and then update them one by one:

UPDATE patient A, patient B, patient C
SET A.fee=A.fee*0.97
WHERE A.sin <> B.sin and A.sin <> C.sin and B.sin <> C.sin
    and A.room=B.room and A.room=C.room
    and A.hospital=B.hospital and A.hospital=C.hospital
    and A.exit_date IS NULL and B.exit_date IS NULL and C.exit_date IS NULL

AS SAID: this answer is a workaround to my problem

answered Nov 24, 2016 at 20:41

icosac

391 silver badge7 bronze badges

Collectives™ on Stack Overflow

MySql using subquery with update

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related