SQlite delete inner join

Question

I have read alot of other answers but cannot get this working!.

select * from invTypes
inner join invGroups on invtypes.groupID = invgroups.groupID
where invGroups.categoryID = 25;

I simply want to delete the record from invTypes if the category ID matches to 25

Would be very grateful of assistance

I have already tried:

delete from invTypes
inner join invGroups on invtypes.groupID = invgroups.groupID
where invGroups.categoryID = 25;

Gives error: near "inner": syntax error:

DELETE FROM invTypes
WHERE groupID in ( SELECT groupID FROM invGroups INNER JOIN invTypes ON (invGroups.groupID = invTypes.groupID) 
WHERE invGroups.categoryID = 25);

Gives: ambiguous column name: groupID: DELETE FROM invTypes WHERE groupID in ( SELECT groupID FROM invGroups INNER JOIN invTypes ON (invGroups.categoryID = invTypes.categoryID) WHERE invGroups.categoryID = 25);

score 8 · Accepted Answer · answered Jan 15 '15 at 20:18

8

you can use delete with a where clause

delete from invTypes
where groupId in ( select groupId from invGroups where CategoryID =25)

answered Jan 15 '15 at 20:18

radar

13,270
2
25
33

Perfect.. I knew I was over complicating it. Thanks! – dai007uk Jan 15 '15 at 20:20

score -2 · Answer 2 · edited Jan 15 '15 at 20:23

-2

DELETE invTypes
FROM invTypes
INNER JOIN invGroups
ON invTypes.groupID = invGroups.groupID
WHERE invGroups.categoryID = 25;

edited Jan 15 '15 at 20:23

Andy

49,085
60
166
233

answered Jan 15 '15 at 20:14

Fernando Martín Besteiro

745
5
16

SQlite delete inner join

2 Answers2

Linked