mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-12 15:33:44 +00:00
147 lines
3.6 KiB
Plaintext
147 lines
3.6 KiB
Plaintext
|
|
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;
|
|
|