SQLite Forum

misuse not detected
Login

misuse not detected

(1) By Rado (antlor) on 2021-04-08 17:24:23 [source]

I found this by accident:

update T set
A = A + 1,
A = A + 1;

Other databases don't allow using column more then once. If you play a little with this you can try this:

update T set
A = A + 1,
A = A - 1;

or

update T set
A = A + 1,
A = A - 1,
A = A + 1;

But I have also another question If I can always rely on how columns are updated by this I mean order. For example:

update T set 
F1 = F2,
F2 = F3,
F3 = F4,
F4 = null;

This is working but can I rely on column update order that
1. F1=F2
2. F2=F3
3. F3=F4
4. F4=null

I should look in sql standard but I use this in other databases never question If I can.

Regards
Radovan

(2.1) By Keith Medcalf (kmedcalf) on 2021-04-08 18:23:46 edited from 2.0 in reply to 1 [link] [source]

Update expressions are executed in sequentially. It appears that if multiple updates in the same statement target the same column then the last one stated is the one that is coded.

SET <lhs> = <rhs>

The statement <lhs> represents the new value that will be written to the database when the statement commits.

The statement <rhs> represents the old value that was read from the database.

An update of the form:

SET A = B, B = A

means that the value of the record written will exchange the values of A and B.

That is the value of A is set to the value of B that existed when the record was read and that the value of B is set to the value of A that existed when the record was read.

(3) By Keith Medcalf (kmedcalf) on 2021-04-08 18:29:00 in reply to 2.1 [link] [source]

That is, the statement is basically:

SET new.* = old.*,
    new.<name> = (expressions computed using old values)

and then when the update is committed the old record is replaced with the new record.

(4) By Rado (antlor) on 2021-04-08 18:34:40 in reply to 2.0 [link] [source]

Yes

SET A = B, B = A 

is working. I was just wondering if this is defined by SQL standard and I could rely on it.

Thanks for confirmation!

Testing this example in MSSQL

update T set
A = A + 1,
A = A + 1

give very descriptive error:
The column name 'A' is specified more than once in the SET 
clause or column list of an INSERT. 
A column cannot be assigned more than one value in the same clause. 
Modify the clause to make sure that a column is updated only once. 
If this statement updates or inserts columns into a view, column
aliasing can conceal the duplication in your code.

or Firebird SQL database error is:
Column A cannot be repeated in update statement