CODEWITHSUNDEEP
sqlite
Joseph_Marzbani
Joseph_Marzbani

Reputation: 1876

UPDATE statement not working in sqlite

I'm using the following command to update a field in my database:

UPDATE Movies SET 'From'=2 WHERE 'Name'="foo";

I'm using sqlite3.exe in windows (command prompt). Although no error message is produced, nothing changes in the table. I examined the database with a couple of gui tools and I'm sure UPDATE does nothing.

'From' is of type integer and 'Name' is text.

Upvotes: 1

Views: 3595

Answers (2)

Donal Fellows
Donal Fellows

Reputation: 137597

The problem you've got is that you're getting your quoting wrong. SQLite follows the SQL standard here, and that specifies what quote characters to use: '…' is for strings, and "…" is for tokens (like special names used as column or table names). Sometimes it manages to guess what you mean anyway and compensate for getting it wrong, but it can't with the WHERE clause because that is syntactically correct (if decidedly unhelpful):

Swapping the quoting appears to work:

UPDATE Movies SET "From"=2 WHERE "Name"='foo';

Upvotes: 5

duffymo
duffymo

Reputation: 308813

Those aren't good column names. Both are keywords, best avoided, and not self-explanatory at all.

Do a SELECT to see how many rows match the WHERE clause in the UPDATE. If none come back, you have your answer.

Did you commit the UPDATE? Is auto commit turned on?

Upvotes: 2

Related Questions