-1

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

James Z
  • 12,209
  • 10
  • 24
  • 44
Harry M.
  • 1
  • 2
  • 1
    From the comments on my answer it is not immediately clear what you're trying to achieve, and the join would be unnecessary in this case anyway. Can you add that to your answer? – Sebastiaan van den Broek Jul 16 '22 at 13:35
  • I'm pretty new to asking questions on here, but the prompt: Joe Barnes has graduated and his information has been recorded in an alumni table. Delete his record from the students table. They want us to use his name and not the ids referencing the students across all tables. I need a query to delete from the students table by name that will also delete his information from the grades table. ``` – Harry M. Jul 16 '22 at 13:45
  • 1
    Sorry I meant to type edit it into your question, not your answer. You should be able to edit your own question to add this information. My answer remains the same though, with the 2 options I gave. – Sebastiaan van den Broek Jul 16 '22 at 13:54

3 Answers3

0

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.

Sebastiaan van den Broek
  • 5,818
  • 7
  • 40
  • 73
  • When I tried to use that I get this error, ERROR: update or delete on table "students" violates foreign key constraint "grades_student_id_fkey" on table "grades" DETAIL: Key (id)=(4) is still referenced from table "grades". SQL state: 2350 – Harry M. Jul 16 '22 at 13:20
  • @HarryM. then the query is actually correct, but you also need to delete the entry in the `grades` table before that too. I'm not really sure what you're trying to do here anyway tbh because the join on grades seems irrelevant. – Sebastiaan van den Broek Jul 16 '22 at 13:22
  • The prompt wants me to delete joe from all records I had it working with using his student id which is mentioned throughout the tables, but my project got kicked back and they want me to use just his name. I want to combine the two tables so joe is referenced in grades and students. – Harry M. Jul 16 '22 at 13:26
  • If you just want to cascade the delete of a student to the grades table too, you can set that when you're creating that foreign key, with the `ON DELETE CASCADE` option. And then you don't need `USING` at all for your query. – Sebastiaan van den Broek Jul 16 '22 at 13:26
  • The alternative is just removing the row from `grades` first and then from `students` after, ideally all in 1 transaction. – Sebastiaan van den Broek Jul 16 '22 at 13:27
0

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;
Ishadi Jayasinghe
  • 122
  • 8
  • You can refer here as well https://stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join – Ishadi Jayasinghe Jul 16 '22 at 13:15
  • The issue with that is that 'Joe' isn't mentioned in the grades table so I get an error. ERROR: update or delete on table "students" violates foreign key constraint "grades_student_id_fkey" on table "grades" DETAIL: Key (id)=(4) is still referenced from table "grades" – Harry M. Jul 16 '22 at 13:23
  • Ah. then you would have to refer the first_name as students.first_name. However, I see you have a foreign constraint on the grades table. You can either delete the record in grades table first. Or if you want that deletion to be automatic when you execute the query above, you can change the foreign key constraint to have To automate this, you could define the foreign key constraint with ON DELETE CASCADE. Will update the answer. – Ishadi Jayasinghe Jul 16 '22 at 13:30
0

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);
nbk
  • 45,398
  • 8
  • 30
  • 47