Delete rows with inner join?

Question

I have a SQLITE database with two tables. Table A has an integer timestamp and another integer column containing a row id referring to a row in table B which has two timestamps.

I want to delete all rows in table A where it's timestamp does not lie between the two timestamps in table B, and the ROWID is equal to X.

Here is what I have at the moment but I am getting a syntax error:

DELETE FROM network
WHERE ROWID in (
    SELECT ROWID 
    FROM track 
    INNER JOIN network ON (track.ROWID = network.trackId) 
    WHERE network.timestamp > track.stopTime OR network.timestamp < track.startTime 
        AND network.trackId = X

Jonathan Hall · Accepted Answer · 2011-12-03 00:19:58Z

17

You don't have a closing parenthesis for your subselect. Try this:

DELETE FROM network
WHERE ROWID in (
    SELECT ROWID 
    FROM track 
    INNER JOIN network ON (track.ROWID = network.trackId) 
    WHERE network.timestamp > track.stopTime OR network.timestamp < track.startTime 
       AND network.trackId = X
)

If that doesn't work, try posting your actual syntax error.

answered Dec 3, 2011 at 0:19

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Max Mumford Over a year ago

Hi, thanks for the post. You were right, that sorted the first syntax error. Also WHERE ROWID should have been WHERE trackId, and SELECT ROWID should have been SELECT track.ROWID. The script now seems to be working. Thanks for your help! :)

Raju Over a year ago

DELETE FROM tblRecipe WHERE categoryID in ( SELECT categoryID FROM tblSubCategories INNER JOIN tblRecipe ON (tblSubCategories.categoryID = tblRecipe.categoryID) WHERE tblRecipe.categoryID = 9;

PirateApp Over a year ago

whnat if you had 2 columns say rowid and something else

Jonathan Hall Over a year ago

@PirateApp: I don't understand your question. I suggest posting a new question with details of your specific query and problem.

Jonathan Hall Over a year ago

As I suggested, you should ask a new question. (Although that question has probably already been asked)

|

Collectives™ on Stack Overflow

Delete rows with inner join?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related