CODEWITHSUNDEEP
javascriptsqlitetitanium
agent86
agent86

Reputation: 95

SQL delete not working as expected

If I put each (one at a time) in the same code the second one will update the title to var testTitle, the first does not delete the rows/items that match the WHERE.

function deleteTableAItem(e) {
    var db3 = Ti.Database.open('thedatabase');

    var getTheID = db3.execute('SELECT ID FROM tableA WHERE myIndexColumn=?', e.itemIndex);
    var theID = getTheID.fieldByName('ID');

    Ti.API.info(tableLinkRef + " " + theID); //they match as intended

    var testTitle = "DID I CHANGE?";

    db3.execute('DELETE FROM tableB WHERE tableLinkRef=? AND tableARef=?',theID,theTableAItemClicked); // fixed by adding another comparison.

    //db3.execute('UPDATE tableB SET titleColumn=? WHERE tableLinkRef=?',testTitle,theID); // this DOES update the titleColumn in matching rows/items when the code is active and the previous is commented out

    db3.execute('DELETE FROM tableA WHERE myIndexColumn=?', e.itemIndex); //this deletes the row from the first table. I want to delete the rows I have associated in tableB as well

    db3.close();
}

Upvotes: 0

Views: 92

Answers (3)

Martin
Martin

Reputation: 1914

I don't understand why you need two pieces of criteria (WHERE tableLinkRef=? AND tableARef=?',theID,theTableAItemClicked) for a DELETE and only one piece of criteria for the UPDATE (WHERE tableLinkRef=? AND tableARef=?',theID,theTableAItemClicked)

db3.execute('DELETE FROM tableB WHERE tableLinkRef=? AND tableARef=?',theID,theTableAItemClicked); // fixed by adding another comparison.

//db3.execute('UPDATE tableB SET titleColumn=? WHERE tableLinkRef=?',testTitle,theID); // this DOES update the titleColumn in matching rows/items when the code is active and the previous is commented out

Since you are saying that the update is working on the correct rows, you would just take the WHERE clause from that command. 

db3.execute('DELETE FROM tableB WHERE tableLinkRef=?',theID);

It appears confusing because tableLinkRef appears to be a reference to tableA's id and tableARef seems to describe that as well.

Upvotes: 1

Chris Lam
Chris Lam

Reputation: 3614

Your DELETE sql in the code looks fine. You may print out the entire SQL generated to see if it is really correct, or see whther sqlite is returning any error.

Upvotes: 1

John Smith
John Smith

Reputation: 7407

if you delete the record before you update it, there is nothing to be updated. I would just move the second line before the first, but do you really want to delete something you just spent time to update?

Upvotes: 0

Related Questions