UPDATE syntax in SQLite

问题:

I need to know if I can do this in an UPDATE statement:

UPDATE users SET ('field1', 'field2', 'field3') 
VALUES ('value1', 'value2', 'value3');

Or similar syntax. I'm using SQLite.

Note:

Nobody understands me, I just want to know if it is possible to SET separate fields to separate values. That's all.

回答1:

There is a (standard SQL) syntax that is similar to what you propose but as far as I know, only Postgres has implemented it:

UPDATE users
SET  (field1, field2, field3)
   = ('value1', 'value2', 'value3') 
WHERE some_condition ;

Tested (for the infidels) in: SQL-Fiddle

---

This also works in Postgres:

UPDATE users AS u
SET 
   (field1, field2, field3)
 = (f1, f2, f3)
FROM
  ( VALUES ('value1', 'value2', 'value3')
  ) AS  x (f1, f2, f3)
WHERE condition ;

---

This works in Postgres and SQL-Server:

UPDATE users 
SET 
   field1 = f1, field2 = f2, field3 = f3
FROM
  ( VALUES ('value1', 'value2', 'value3')
  ) AS  x (f1, f2, f3)
WHERE condition ;

---

and as @JackDouglas commented, this works in Oracle:

UPDATE users
SET  (field1, field2, field3)
   = ( SELECT 'value1', 'value2', 'value3' FROM dual ) 
WHERE condition ;

回答2:

What you posted is not the correct syntax for UPDATE. To UPDATE your syntax is:

UPDATE users 
SET field1 = 'value1',
    field2 = 'value2',
    field3 =  'value3';

You will want to add a WHERE clause or this will UPDATE all records.

回答3:

If you want to update a record, it should look like this. You should very rarely want to update without a WHERE condition.

UPDATE users
SET field1='value1',
    field2='value2',
    field3='value3'
WHERE field1=1

SQL Fiddle

回答4:

Try

UPDATE users SET field1='value1', field2='value2'

etc...

You can find the official docs here.

W3Schools also has a useful page:

SQL UPDATE Statement

The UPDATE statement is used to update records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

EDIT: since you seem to need to create a string, the normal method to do so safely is to use prepared statements.

Assuming Java, we already have this example on StackOverflow:

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("SELECT * FROM Country WHERE code = ?");
stmt.bindString(1, "US");
stmt.execute();

In your case,

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("UPDATE users SET field1=?, field2=?...");
stmt.bindString(1, "value1");
stmt.bindString(2, "value2");
stmt.execute();