Mysql : insert into select && where not exists

Question

I need to insert some data in a table named ‘queue’ which is a patient queue in a particular date . Two fields data will be inserted .Two fields name are ‘PatientID’ and ‘Visiting Date’. Table ‘queue' like

QueueID      |         PatientID |           Visiting_date |
-------------|-------------------|-------------------------|
      1      |   4               |      Current date       |

table:queue

But while inserting the record there are two conditions :

Condition 1 : patitentID comes from patient table (given below) Condition 2 : one record will be inserted to ‘queue’ table if it does not exist to prevent repeatation.ie PatientID=4 will not be inserted if already inserted.

-------------|-----------------|------------------|
 patitentID  |    Patient Name |    Contact no    |
-------------|-----------------|------------------|
     4       |  David          |  01245785874     |

table:patient

My SQL is: (it does not work)

INSERT INTO `queue`(`patientID`, `Visiting_date`)
SELECT  patient.`patientID`,’CURDATE()’ FROM `patient`
WHERE NOT EXISTS (
    SELECT `patientID`, `visiting_date`FROM  `queue`
 WHERE `patientID` = '4' AND `visting_date`=CURDATE()
) LIMIT 1;

What error do you get? – Sven van den Boogaart Sep 27 '16 at 10:03 — Sven van den Boogaart, Sep 27 '16 at 10:03

score 1 · Answer 1 · edited May 23 '17 at 12:15

You could set a foreign key to make sure the patients id exists.

In the Queue table you can set patientID as unique, this makes sure you can insert only unique id's in the queue table.

Also if you would like to be able to insert the same userID but with different dates you could specify unique constraint for multiple columns in MySQL.

If you want to solve it with a mysql query only you can use this question.

score 0 · Answer 2 · answered Sep 27 '16 at 08:07

I would use a separate query to check if there is a user with that ID in that table.

SELECT * FROM queue WHERE PatientID = 4;

and then check the result of that query, if it returns a row, that means that there is a user in there and you don't do anything.

If the query doesn't return a row, that means you can now use a query to inert a user. Like this

INSERT INTO queue (PatientID, VisitingDate);

Mysql : insert into select && where not exists

2 Answers2