midrange.com code scratchpad |
Name:
overriding generated columns
|
Scriptlanguage:
Plain Text
|
Tabwidth:
4
|
Date:
02/07/2018 06:30:17 pm
|
IP:
Logged
|
|
Description:
The user was to lazy to give a description
|
Code:
- drop table qtemp.testtable;
- create table qtemp.testtable (
- "ID" INTEGER GENERATED ALWAYS AS IDENTITY (
- START WITH 1 INCREMENT BY 1
- NO MINVALUE NO MAXVALUE
- NO CYCLE NO ORDER
- CACHE 20 ),
- mytext char(20));
-
- INSERT INTO QTEMP.TESTTABLE (MYTEXT) VALUES('A');
-
- SELECT * FROM QTEMP.TESTTABLE; -- "ID"=1
-
- UPDATE QTEMP.TESTTABLE OVERRIDING USER VALUE
- SET ID =0, MYTEXT='B'
- WHERE ID=1;
- SELECT * FROM QTEMP.TESTTABLE; -- "ID=2
- -- OVERRIDING USER VALUE says to ignore the ID=0 and instead generate a new identity value for that column.
-
- UPDATE QTEMP.TESTTABLE OVERRIDING USER VALUE
- SET MYTEXT='C'
- WHERE ID=2;
- -- Fails. You cannot specify OVERRIDING and not update any columns with defaults.
-
- UPDATE QTEMP.TESTTABLE
- SET MYTEXT='C'
- WHERE ID=2;
- SELECT * FROM QTEMP.TESTTABLE; -- ID still =2
- -- Since you didn't include ID on your SET clause it didn't update it.
-
- UPDATE QTEMP.TESTTABLE OVERRIDING SYSTEM VALUE
- SET ID =0, MYTEXT='D'
- WHERE ID=2;
- SELECT * FROM QTEMP.TESTTABLE; -- "ID=0
- -- OVERRIDING SYSTEM VALUE directly says do what I say and put 0 in there and do not let the system adjust it.
-
- -- OVERRIDING USER VALUE is the default
-
|
|
|