I met the same issue. REPLACE is the right way to override it.
Assuming C1 is the primary key or at least unique,
instead of
UPDATE T1 INNER JOIN T2 ON T1.C1=T2.C1
SET T1.C2=T2.C2
WHERE T1.C2<>T2.C2;
you can write:
REPLACE INTO T1
SELECT T1.C1,T2.C2,C3,C4 FROM T1 INNER JOIN T2 ON T1.C1=T2.C1
WHERE T1.C2<>T2.C2;
Beware, SELECT shall include the full T1 column set
Samuel Neff a écrit :
> 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
>
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
