CODEWITHSUNDEEP
mysqlsqldelete-row
CodeGust
CodeGust

Reputation: 844

SQL Delete with relation to another table

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.

Upvotes: 0

Views: 103

Answers (3)

Peter Bowers
Peter Bowers

Reputation: 3093

http://dev.mysql.com/doc/refman/5.0/en/delete.html

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.

Upvotes: 1

Dan
Dan

Reputation: 11104

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>;

Upvotes: 1

Gleiemeister 2000
Gleiemeister 2000

Reputation: 729

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

Upvotes: 1

Related Questions