MySQL Delete Row Based On Column Name

Question

Table2 has a column with values containing column names in table1. I want to remove any entries in table2 where the actual column name in table1 does not exist. This is the general idea but apparently isn't the proper approach so how can it be done?

DELETE FROM table2 
WHERE ID IN (SELECT 
ID 
FROM table2 
WHERE FormID = 2 
AND FieldName NOT IN (SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME =  'table1'))

@Barmar How is this in any way a duplicate? The other is about deleting from one table based on content of another but this question is how to delete from one table based on an actual column name from another. Not even close to the same thing. — DonP, Sep 04 '18 at 16:32
You're trying to delete from `table2`, and the `WHERE ID IN` subquery uses `table2`. That's not allowed in MySQL, and the other question explains how to fix it. — Barmar, Sep 04 '18 at 16:33

ScaisEdge · Accepted Answer · 2018-09-04T16:36:34.890

1

could be you can't delete rows from the same table you are selecti so try using a subquery instead of a IN clause

DELETE table2 FROM table2 
INNER JOIN  (
   SELECT ID 
    FROM table2 
    WHERE FormID = 2 
    AND FieldName NOT IN (
        SELECT COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'db_name' 
        AND TABLE_NAME =  'table1'
      )
) t on t.ID = table2.ID

edited Sep 04 '18 at 16:36

answered Sep 04 '18 at 08:21

ScaisEdge

131,976
10
91
107

Thank you, that looks good, making more sense than my original query, and I'll check it out tomorrow to be sure I didn't introduce errors when pasting it in and putting the actual values back in as a quick test gives a syntax error. – DonP Sep 04 '18 at 08:34
@DonP .. let me know if the asnwer is right or if you have some error – ScaisEdge Sep 04 '18 at 08:38
I’m more awake now but still getting the syntax error on line 2. Running the two selects together without the rest does give the expected ID value but the DELETE has no WHERE. – DonP Sep 04 '18 at 16:29
When you use a join in a `DELETE` query, you need to specify the table that you're deleting from: `DELETE table2 FROM table2 ...` – Barmar Sep 04 '18 at 16:36
answer updated .. delete table from table – ScaisEdge Sep 04 '18 at 16:36
@Barmar .. thanks for the suggestion .. – ScaisEdge Sep 04 '18 at 16:37
@DonP . the ON clause in a JOIN is the where clause .. – ScaisEdge Sep 04 '18 at 16:54
Works great now, thank you! – DonP Sep 04 '18 at 17:10

MySQL Delete Row Based On Column Name

1 Answers1