SQLite 中的更新语法

2022-01-17 00:00:00 sql-update sql sqlite

我需要知道是否可以在 UPDATE 语句中执行此操作:

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

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

或类似的语法.我正在使用 SQLite.

Or similar syntax. I'm using SQLite.

注意:

没有人理解我,我只想知道是否可以将单独的字段设置为单独的值.就是这样.

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

推荐答案

有一个(标准 SQL)语法与您提出的类似,但据我所知,只有 Postgres 实现了它:

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 ;

测试(针对异教徒):SQL-Fiddle

Tested (for the infidels) in: SQL-Fiddle

这也适用于 Postgres:

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 ;

<小时>

这适用于 Postgres 和 SQL-Server:


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 ;

<小时>

正如@JackDouglas 评论的那样,这适用于甲骨文:


and as @JackDouglas commented, this works in Oracle:

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

相关文章