CODEWITHSUNDEEP
sqlsqlite
Takarii
Takarii

Reputation: 1648

SQLite - Why wasn't this thrown as a syntax error?

I just spent the last hour trying to figure out why my SQLite database wasnt updating.

The query being done was

UPDATE Table SET col1 = val AND col2 = val2 AND col3 = val3 WHERE col1 = val4

Obviously this is the wrong syntax for an update query, however no exceptions were thrown, no errors shown and when run directly using SQLiteBrowser it even said that the query executed correctly and had updated a row (yet no update was done)

Can anyone explain why this query wasn't rejected with an exception or syntax error?

Update:

So after looking at things further, I can see that an update was indeed performed, however instead of setting col1 to the new value, it was instead set to 0 which seems odd.

Upvotes: 1

Views: 43

Answers (1)

Matt Gibson
Matt Gibson

Reputation: 38238

The AND operator in most languages, including SQLite, can be used in expressions (for boolean or bitwise operations, etc.), and the = operators is testing for equality, so you're just setting col1 equal to the value of the expression val AND col2 = val2 AND col3 = val3.

For example, if all the columns and values are 1 in your expression, you're setting col1 to:

1 AND 1 = 1 AND 1 = 1

...which is a valid expression that evaluates to 1.

SQLite will report as many rows as matched by the WHERE clause to have been affected by the update, even if the new value in col1 was the same as the old value, i.e. even if the data isn't actually changing. It still found rows to update and put a new value in there, so it reports having successfully performed an update.

We'd need to know the types/values of the columns to work out exactly what's going on, but I don't see a syntax error there, though it's clear you didn't mean to do what the expression (validly!) says!

Upvotes: 2

Related Questions