DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) WITHOUT ROWID;
INSERT INTO t2 VALUES(1, 2, 3);
INSERT INTO t2 VALUES(4, 5, 6);
INSERT INTO t2 VALUES(7, 8, 9);
DROP TABLE IF EXISTS chng;
CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER);
INSERT INTO chng VALUES(1, 100, 1000);
INSERT INTO chng VALUES(7, 700, 7000);
SELECT * FROM t2;
UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a;
SELECT * FROM t2 ORDER BY a;
DELETE FROM t2;
INSERT INTO t2 VALUES(1, 2, 3);
INSERT INTO t2 VALUES(4, 5, 6);
INSERT INTO t2 VALUES(7, 8, 9);
UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a)
WHERE a IN (SELECT a FROM chng);
SELECT * FROM t2 ORDER BY a;
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) WITHOUT ROWID;
INSERT INTO t3 VALUES(1, 1, 'one');
INSERT INTO t3 VALUES(2, 2, 'two');
INSERT INTO t3 VALUES(3, 3, 'three');
DROP TABLE IF EXISTS t4;
CREATE TABLE t4(x TEXT);
INSERT INTO t4 VALUES('five');
SELECT * FROM t3 ORDER BY a;
UPDATE t3 SET c=x FROM t4;
SELECT * FROM t3 ORDER BY a;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) ;
INSERT INTO t2 VALUES(1, 2, 3);
INSERT INTO t2 VALUES(4, 5, 6);
INSERT INTO t2 VALUES(7, 8, 9);
DROP TABLE IF EXISTS chng;
CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER);
INSERT INTO chng VALUES(1, 100, 1000);
INSERT INTO chng VALUES(7, 700, 7000);
SELECT * FROM t2;
UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a;
SELECT * FROM t2 ORDER BY a;
DELETE FROM t2;
INSERT INTO t2 VALUES(1, 2, 3);
INSERT INTO t2 VALUES(4, 5, 6);
INSERT INTO t2 VALUES(7, 8, 9);
UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a)
WHERE a IN (SELECT a FROM chng);
SELECT * FROM t2 ORDER BY a;
DROP TABLE IF EXISTS t3;
CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) ;
INSERT INTO t3 VALUES(1, 1, 'one');
INSERT INTO t3 VALUES(2, 2, 'two');
INSERT INTO t3 VALUES(3, 3, 'three');
DROP TABLE IF EXISTS t4;
CREATE TABLE t4(x TEXT);
INSERT INTO t4 VALUES('five');
SELECT * FROM t3 ORDER BY a;
UPDATE t3 SET c=x FROM t4;
SELECT * FROM t3 ORDER BY a;
DROP TABLE IF EXISTS t5;
DROP TABLE IF EXISTS m1;
DROP TABLE IF EXISTS m2;
CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT);
CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT);
CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT);
INSERT INTO t5 VALUES(1, 'one', 'ONE');
INSERT INTO t5 VALUES(2, 'two', 'TWO');
INSERT INTO t5 VALUES(3, 'three', 'THREE');
INSERT INTO t5 VALUES(4, 'four', 'FOUR');
INSERT INTO m1 VALUES(1, 'i');
INSERT INTO m1 VALUES(2, 'ii');
INSERT INTO m1 VALUES(3, 'iii');
INSERT INTO m2 VALUES(1, 'I');
INSERT INTO m2 VALUES(3, 'II');
INSERT INTO m2 VALUES(4, 'III');
UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a;
SELECT * FROM t5 ORDER BY a;
CREATE TABLE t0(a);
CREATE TABLE t1(b);
UPDATE t1 SET b=sum(a) FROM t0;
SELECT * FROM t0, t1;
CREATE TABLE t1(x INT); INSERT INTO t1 VALUES(1);
CREATE TABLE t2(y INT); INSERT INTO t2 VALUES(2);
WITH t1 AS (SELECT y+100 AS x FROM t2)
UPDATE t1 SET x=(SELECT x FROM t1);
SELECT x, y FROM t1, t2;
WITH t1 AS (SELECT y+100 AS x FROM t2)
UPDATE t1 SET x=x+y FROM t2;
SELECT x, y FROM t1, t2;
CREATE TABLE t1(a);
INSERT INTO t1(a) VALUES(5);
CREATE VIEW t2 AS SELECT a FROM t1 UNION ALL SELECT a FROM t1;
CREATE TABLE t3(b,c);
INSERT INTO t3(b,c) VALUES(1,2);
UPDATE t3 SET (c,b) = (SELECT 3,4) FROM t1, t2;
SELECT * FROM t3;