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

2008-08-22 Thread Samuel Neff 
Thanks, I didn't think REPLACE would work here but you're right, it does do
exactly what I need.

Best regards,

Sam


-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]


On Fri, Aug 22, 2008 at 3:13 AM, Francis GAYREL <[EMAIL PROTECTED]>wrote:

> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

2008-08-22 Thread Francis GAYREL 
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
> SETA = 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
>
> UPDATET1
> SETA = (
> 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

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

2008-08-21 Thread 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
SETA = 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

UPDATET1
SETA = (
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