SQL Delete with relation to another table

Question

I have two tables, for example:

TableA with columns GUID,ProgKey,UserKey,ProgName.

TableB with columns GUID,AppKey,ModeName. Foreign Key: GUID->TableA.GUID

I need to delete TableB.AppKey with specific value but only with the condition where TableA.UserKey= specific value.

I tried something simple like this but it didn't work:

Delete from TableB b, TableA a where b.AppKey=? and a.UserKey=?

And tried this, also didn't work:

Delete from TableB (AppKey) Select ? From TableB b, TableA a where a.UserKey=?

?=appkeyValue000

?=userkeyValue000

GUID is a primary key.

See http://stackoverflow.com/questions/652770/delete-with-join-in-mysql for a response several years old that has several comments relating to which is best. — Peter Bowers, Feb 25 '15 at 07:27
@PeterBowers Thank you! That has made things clear for me. Will use join instead of "and in (select...)" — CodeGust, Feb 26 '15 at 04:09

score 1 · Answer 1 · answered Feb 24 '15 at 16:36

1

delete from table where id in (select id from table2 where y=234)?

answered Feb 24 '15 at 16:36

Gleiemeister 2000

score 1 · Answer 2 · answered Feb 24 '15 at 16:40

1

You can delete using a JOIN, which seems like the most natural way IMO.

DELETE TableB
FROM TableB JOIN TableA ON TableA.GUID = TableB.GUID
WHERE TableB.AppKey = <Value> AND TableA.UserKey = <OtherVal>;

answered Feb 24 '15 at 16:40

Dan

score 1 · Accepted Answer · answered Feb 24 '15 at 16:51

1

DELETE tableB FROM tableB JOIN tableA ON (tableB.GUID = tableA.GUID)
WHERE tableA.UserKey = 'userkeyValue000'
AND tableB.AppKey = 'appkeyValue000'

The deletion occurs in the table(s) occurring before the FROM keyword.

answered Feb 24 '15 at 16:51

Peter Bowers

Thank you! It works! However, I am not sure which answer to use: this one (seems to be lighter) or Table-JOIN... Is there a difference? – CodeGust Feb 24 '15 at 22:09

3 Answers3