CODEWITHSUNDEEP
mysqlsql
Aurora Bravo
Aurora Bravo

Reputation: 15

SQL: How to Delete rows that match from table2 AND key from table3

I have 3 tables:

Table1: entitylink_test
parent_entity_id | child_entity_id
           1              |        11
           1              |        12
           2             |          13

Table2: vm_host_id
vm_id                 |    host_id
         11              |        1
          12             |        1
          14             |          15

Table3: tagstorage
entity_id              |    tag_id
         11              |        80
          12             |        85
          20             |         80

I want to delete from entitylink_test the rows where entitylink_test.child_entity_id = vm_host_id.vm_id and where tagstorage = 80

So far, I have tried

DELETE FROM entitylink_test
WHERE entitylink_test.child_entity_id = vm_host_id.vm_id
AND tagstorage.tag_id = 80;

And I've tried

DELETE entitylink_test
FROM entitylink_test INNER JOIN vm_host_id
    ON entitylink_test.child_entity_id = vm_host_id.vm_id
    INNER JOIN tagstorage
      ON entitylink_test.child_entity_id = tagstorage.entity_id
      WHERE tagstorage = 80;

However, I keep getting the error "Unknown column 'vm_host_id.vm_id' in 'where clause'" for the first code, and "Unknown column 'tagstorage' in 'where clause'" for the second. Any other suggestions for syntax?

Any help is appreciated,
Thank you!

Upvotes: 0

Views: 33

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

I think you mean:

DELETE entitylink_test
FROM entitylink_test INNER JOIN vm_host_id
    ON entitylink_test.child_entity_id = vm_host_id.vm_id
    INNER JOIN tagstorage
      ON entitylink_test.child_entity_id = tagstorage.entity_id
      WHERE tagstorage.tag_id = 80;

You were referring to the table tagstorage, a table doesn't have a value, so you should specify which attribute of the table should have value 80.

Upvotes: 3

Related Questions