How to write a DELETE statement? My SELECT statement works, but when I switch to DELETE I get an err-CodePudding

This SELECT statement works:

SELECT * 
FROM students 
JOIN grades ON grades.student_id = students.id
WHERE first_name = 'Joe';

I then switch to DELETE:

DELETE FROM students
JOIN grades ON grades.student_id = students.id
WHERE first_name = 'Joe';

And then I get an error:

ERROR: syntax error at or near "JOIN"
LINE 2: JOIN grades on grades.student_id = students.id
^
SQL state: 42601
Character: 22

CodePudding user response：

PostgreSQL doesn’t support the DELETE JOIN statement. However, it does support the USING clause in the DELETE statement that provides similar functionality as the DELETE JOIN. You may try below.

DELETE FROM students USING grades WHERE grades.student_id = students.id and first_name = 'Joe';

-- Update. To have the record in grades table deleted automatically, you can alter grades table to have ON DELETE CASCADE on its fk contraint. Like below for example.

   ALTER TABLE grades
   DROP CONSTRAINT grades_student_id_fkey
   ADD  CONSTRAINT grades_student_id_fkey
   FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE;

CodePudding user response：

You can't JOIN in a DELETE statement in Postgres, you have to use USING

DELETE FROM students s
USING grades g
WHERE g.student_id = s.id AND s.first_name = 'Joe';

However, I'm not really sure why you're joining in the first place since (I assume) first_name is supposed to be part of the students table. if you want to delete the grades for this student then you need to perform the delete on the grades table instead. Or if you just want to delete a student with all the data pointing at it, you need to set those foreign keys with the option ON DELETE CASCADE or delete those rows first, ideally in the same transaction.

CodePudding user response：

PostGRES doesn support JOINS in delete, so you need another approach

DELETE FROM students 
 WHERE first_name = 'Joe' 
       and students.id in (Select student_id FROM grades WHERE student_id = students.id);