MySQL esoteric DELETE FROM Syntax
I had a little problem today using DELETE statement in MySQL. I wanted to delete those rows, unreferenced by another table. So what comes to ones mind is using a subselect like
DELETE FROM table1 WHERE table1.primaryKey NOT IN ( SELECT referenceToTable1 FROM table2)
unfortunately the deployed version of mysql did not allow subselects. In order to avoid querying the uids to a file and the using one’s fav. editor to create an appropriate query, i poke around the mysql website to find this gem:
DELETE table1 FROM table1,table2 WHERE table1.primaryKey<>table2.referenceToTable1
that has the same effect. They call it ‘Multiple-table syntax’. Is this syntax weird, or does any other RDBMS use such a thing?