WHERE NOT EXISTS

Question

I have a simple table with two columns "referralID" & "studentID"

I can add like this

$stmt = $pdo->prepare('INSERT INTO referralStudents (referralID,studentID) VALUES (?, ?)');
$stmt->execute([$myID,$studentID]);

I'm trying to get the WHERE NOT EXISTS statement to work. If there is already a row with both "referralID" & "studentID" don't add.

Both of these don't work can you show me where I'm going wrong?

$stmt = $pdo->prepare('INSERT INTO referralStudents (referralID,studentID) VALUES (?, ?) WHERE NOT EXISTS (SELECT * FROM referralStudents WHERE referralID = ? and studentID = ?")');
$stmt->execute([$myID,$studentID,$myID,$studentID]);
        
$stmt = $pdo->prepare('INSERT INTO referralStudents (referralID,studentID) VALUES (?, ?) WHERE NOT EXISTS (referralID,studentID) VALUES (?, ?)');
$stmt->execute([$myID,$studentID,$myID,$studentID]);

https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — Allen King, Nov 25 '20 at 17:09

forpas · Accepted Answer · 2020-11-25T17:09:16.737

2

You need a SELECT statement and not VALUES to apply the conditions of the WHERE clause:

INSERT INTO referralStudents (referralID,studentID) 
SELECT ?, ? 
FROM dual
WHERE NOT EXISTS (SELECT * FROM referralStudents WHERE referralID = ? and studentID = ?)

You may remove FROM dual if your version of MySql is 8.0+.

See a simplified demo.

edited Nov 25 '20 at 17:09

answered Nov 25 '20 at 17:03

forpas

117,400
9
23
54

score 1 · Answer 2 · answered Nov 25 '20 at 16:55

If the fields are empty i bet they are NULL. A field filled with NULL is existing but its content is NULL (NULL doesnt mean empty or non existing). Maybe this is what you need:

INSERT INTO refferalStudents (refferalID, studentID) VALUES (?,?) WHERE refferalID IS NULL AND studentID IS NULL

WHERE NOT EXISTS

2 Answers2