0

Given this example code:

DELETE d
FROM discog d
JOIN discog d1 ON d1.artist = d.artist AND d1.track = d.track AND d1.year < d.year;

I don't understand the role of the d that comes after DELETE. From what I understand, the structure of the syntax should be

DELETE FROM table_name

WHERE some_column = some_value

What did the author include a d after the DELETE for? What does this position represent?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
ptrcao
  • 421
  • 1
  • 5
  • 19
  • Possible duplicate of [Delete with Join in MySQL](https://stackoverflow.com/questions/652770/delete-with-join-in-mysql), [How to Delete using INNER JOIN with SQL Server?](https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server), [INNER JOIN works as SELECT but not as DELETE](https://stackoverflow.com/questions/24295670/inner-join-works-as-select-but-not-as-delete?noredirect=1&lq=1) – Sinto Jan 22 '19 at 13:04

2 Answers2

2

That is the syntax for an inner join delete. The d mean you want to delete records out of the table aliased as d, which is discog.

dfundako
  • 8,022
  • 3
  • 18
  • 34
1

This delete has two table references:

DELETE d
    FROM discog d JOIN
         discog d1
         ON d1.artist = d.artist AND d1.track = d.track AND d1.year < d.year;

In this case, the references are to the same table, but to different rows.

How would MySQL know which rows to delete? You might want MySQL to just delete from the first referenced table, but explicitly including the alias is a better solution -- it requires the write of the query to be really clear about the intention of the code.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Why does discog have two aliases `d` and `d1`? Couldn't we just as well use `d` for both? It's just the same table? – ptrcao Jan 22 '19 at 18:21
  • @ptrcao . . . Not at all. You need to distinguish between the references to the tables. Otherwise, how would the inequality work? – Gordon Linoff Jan 22 '19 at 22:26