2010/8/5 Dominique Pellé <[email protected]>:
> Using information in previous reply, I can do it with 2 UPDATE queries
> as follows (but I suspect that there is a better solution).
>
> UPDATE t1 SET l_nm = (SELECT l_nm FROM t2 WHERE t1.ID_PK = t2.ID_FK)
> WHERE ID_PK IN (SELECT ID_FK FROM t2);
>
> UPDATE t1 SET r_nm = (SELECT r_nm FROM t2 WHERE t1.ID_PK = t2.ID_FK)
> WHERE ID_PK IN (SELECT ID_FK FROM t2);
For this kind of statements you can use either:
UPDATE t1 SET l_nm = (SELECT l_nm FROM t2 WHERE t1.ID_PK = t2.ID_FK),
r_nm = (SELECT r_nm FROM t2 WHERE t1.ID_PK = t2.ID_FK)
WHERE ID_PK IN (SELECT ID_FK FROM t2);
or
INSERT OR REPLACE INTO t1(ID_PK, l_nm, r_nm)
SELECT t1.ID_PK, t2.l_nm, t2.r_nm
FROM t1 INNER JOIN t2 ON ( t2.ID_FK = t1.ID_PK )
Including t1 in the select statement above is necessary in order not
to add rows, but only keep existing ones, and id_pk must be declared
as primary key.
Regards,
--
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
