[sqlite] Any equivalent to MSSQL's UPDATE..FROM.. clause ?

[Samuel Neff]

I'm trying to update records in one table based on joined data in another
table.  MSSQL has support for a "FROM" clause within an UPDATE statement
which makes this type of thing very easy.  Is there any equivalent in
SQLite?  The only way I've found to achive the same results is to use a
subselect within the SET clause of the UPDATE statement, but that requires
duplicating the WHERE clause within the subselect which is a lot of extra
typing and I'm sure a lot of extra work for SQLite.

MSSQL:

UPDATE T1
SET
A = T2...,
B = T2...,
C = T2...,
FROM T1 INNER JOIN T2 ON ....

SQLite:

UPDATE T1
SET
A = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
B = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
C = (SELECT ... FROM T1 T1_Inner INNER JOIN T2 ON ... WHERE T1_Inner.RowID =
T1.RowID),
...

Here are samples of equivalent code in MSSQL and SQLite.  Is there a way to
simplify the UPDATE statement in the SQLite code?

I'm not replacing the target row entirely, I don't think INSERT OR REPLACE
will work in this scenario.

Thanks,

Sam

--
-- MSSQL
--

CREATE TABLE #T1(
    ID INTEGER PRIMARY KEY IDENTITY,
    A VARCHAR(100),
    B VARCHAR(100),
    C VARCHAR(100));

CREATE TABLE #T2(
    ID INTEGER PRIMARY KEY IDENTITY,
    A VARCHAR(100),
    B VARCHAR(100),
    C VARCHAR(100));

INSERT INTO #T1 VALUES ('a1', 'b1', 'c1');
INSERT INTO #T1 VALUES (NULL, 'b2', 'c2');
INSERT INTO #T1 VALUES ('a3', NULL, 'c3');
INSERT INTO #T1 VALUES ('a4', 'b4', NULL);

INSERT INTO #T2 VALUES ('A1', 'B1', 'C1');
INSERT INTO #T2 VALUES ('A2', NULL, 'C2');
INSERT INTO #T2 VALUES ('A3', 'B3', NULL);
INSERT INTO #T2 VALUES (NULL, 'B4', 'C4');

SELECT * FROM #T1;
SELECT * FROM #T2;

UPDATE    #T1
SET        A = COALESCE(#T1.A, #T2.A),
        B = COALESCE(#T1.B, #T2.B),
        C = COALESCE(#T1.C, #T2.C)
FROM    #T1 INNER JOIN #T2 ON #T1.ID = #T2.ID;

SELECT * FROM #T1;

DROP TABLE #T1;
DROP TABLE #T2;

--
-- SQLite
--

CREATE TEMP TABLE T1(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    A TEXT,
    B TEXT,
    C TEXT);

CREATE TEMP TABLE T2(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    A TEXT,
    B TEXT,
    C TEXT);

INSERT INTO T1 VALUES (NULL, 'a1', 'b1', 'c1');
INSERT INTO T1 VALUES (NULL, NULL, 'b2', 'c2');
INSERT INTO T1 VALUES (NULL, 'a3', NULL, 'c3');
INSERT INTO T1 VALUES (NULL, 'a4', 'b4', NULL);

INSERT INTO T2 VALUES (NULL, 'A1', 'B1', 'C1');
INSERT INTO T2 VALUES (NULL, 'A2', NULL, 'C2');
INSERT INTO T2 VALUES (NULL, 'A3', 'B3', NULL);
INSERT INTO T2 VALUES (NULL, NULL, 'B4', 'C4');

SELECT * FROM T1;
SELECT * FROM T2;


-- here's the ugly statement I'd like to simplfy

UPDATE    T1
SET    A = (
        SELECT COALESCE(InnerT1.A, T2.A)
        FROM T1 InnerT1, T2
        WHERE InnerT1.ID = T1.ID
          AND T2.ID = T1.ID
        ),
       B = (
        SELECT COALESCE(InnerT1.B, T2.B)
        FROM T1 InnerT1, T2
        WHERE InnerT1.ID = T1.ID
          AND T2.ID = T1.ID
        ),
       C = (
        SELECT COALESCE(InnerT1.C, T2.B)
        FROM T1 InnerT1, T2
        WHERE InnerT1.ID = T1.ID
          AND T2.ID = T1.ID
        )
;

SELECT * FROM T1;

DROP TABLE T1;
DROP TABLE T2;


-----------------------------------------------------------------
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users