Update Syntax in Sqlite

UPDATE syntax in SQLite

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 ;

Sqlite3 UPDATE FROM (VALUES) syntax error

SQLite does not support aliasing columns that come from a subquery using VALUES.

You can use the aliases column1, column2:

UPDATE access_keys AS ak 
SET global_id = v.column2 
FROM (VALUES (1, 123),(2, 321)) AS v
WHERE v.column1 = ak.id;

Or, simpler, use a CTE:

WITH cte(id, global_id) AS (VALUES (1, 123),(2, 321))
UPDATE access_keys AS ak 
SET global_id = c.global_id 
FROM cte AS c
WHERE c.id = ak.id;

need help on update syntax error in sqlite?

For each new pressure in combination with a new ctrl I want to insert
the value. If I already have that combination I want to update it

This means that you want the combination of ctrl and pressure to be unique and you can implement this with a unique index:

CREATE UNIQUE INDEX id_ctrl_pressure ON m0_curve(ctrl, pressure);

Then use UPSERT to insert new rows:

INSERT INTO m0_curve (ctrl, flow, power, pressure) VALUES ('0', '0', '0', '117.21')
ON CONFLICT(ctrl, pressure) DO UPDATE 
SET flow = EXCLUDED.flow, 
    power = EXCLUDED.power;

See the demo.

Update values in sqlite database when there are multiple with the same name

If your version of SQLite is 3.33.0+, then use the UPDATE...FROM syntax like this:

UPDATE portfolio AS p
SET shares = (p.id = t.id) * t.shares_left
FROM (
  SELECT MIN(id) id, username, symbol, shares_left 
  FROM (
    SELECT *, SUM(shares) OVER (ORDER BY id) - ? shares_left -- change ? to the number of stocks the user sold 
    FROM portfolio
    WHERE username = ? AND symbol = ?
  ) 
  WHERE shares_left >= 0
) AS t
WHERE p.username = t.username AND p.symbol = t.symbol AND p.id <= t.id;

The window function SUM() returns an incremental sum of the shares until it reaches the number of shares sold.

The UPDATE statement will set, in all rows with id less than than the first id that exceeds the sold stocks, the column shares to 0 and in the row with with id equal to the first id that exceeds the sold stocks to the difference between the incremental sum and the number of sold shares.

See a simplified demo.

For prior versions you can use this:

WITH 
  cte AS (
    SELECT MIN(id) id, username, symbol, shares_left 
    FROM (
      SELECT *, SUM(shares) OVER (ORDER BY id) - ? shares_left -- change ? to the number of stocks the user sold  
      FROM portfolio
      WHERE username = ? AND symbol = ?
    ) 
    WHERE shares_left >= 0
  )
UPDATE portfolio
SET shares = (id = (SELECT id FROM cte)) * (SELECT shares_left FROM cte) 
WHERE (username, symbol) = (SELECT username, symbol FROM cte) AND id <= (SELECT id FROM cte)

See a simplified demo.

Ambiguous column name in SQLite using update and set

Your syntax is wrong.

There is no need to refer to the updated table after FROM and the ON clause must be replaced with a WHERE clause.

This is the correct syntax (if your SQLite version is 3.33.0+) for a join-like UPDATE satement:

update nota
set subtot = nota.cantidad * producto.precio -- subtot must not be qualified with nota.
from producto 
where producto.clave_prod = nota.clave_prod1;

or with aliases:

update nota AS n
set subtot = n.cantidad * p.precio -- subtot must not be qualified with n.
from producto AS p
where p.clave_prod = n.clave_prod1;

Updating Multiple Columns in sqlite3

The syntax for an UPDATE statement is (for SQLite 3.15.0+):

UPDATE Class SET (ClassType,Location,UserID,Staff,Time) = (?,?,?,?,?)

or in standard SQL:

UPDATE Class SET 
  ClassType = ?,
  Location = ?, 
  UserID = ?,
  Staff = ?,
  Time = ?

but I think that you also need a WHERE clause, because this will update all the rows of the table.

---

Related Topics