Re: Inserting data in a table using sub-selects]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 10 March 2004 21:56, Marty Scholes wrote:
> INSERT INTO table2 (id, content) (
>     SELECT id, coalesce(title, '') || ' ' || coalesce(description, '')
>     FROM table1 t1);
>
> If you want to keep them syncrhonized, in other words, rerun the query
> over and over again without having to truncate table2 first or deleting
> all of the rows, you can:
>
> INSERT INTO table2 (id, content) (
>     SELECT id, coalesce(title, '') || ' ' || coalesce(description, '')
>     FROM table1 t1)
> WHERE id NOT IN (
>     SELECT id
>     FROM table1);
>
> With Oracle there is a slick way to do a partial outer join that allowed
> you to do this without creating a complete list of table1.id in the last
> subquery, but I dunno if Pg has an equivalent mechanism.

Thanks, works perfectly.

- --
Andreas Joseph Krogh <andreak@officenet.no>
Managing Director, Senior Software Developer
OfficeNet AS

I always do a CVS update before making a patch (unless I forget).

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFAT5fCUopImDh2gfQRArmFAJ4qx31Wx31sehCBo7qfjPwwPWvUzACguw9t
H8mWrl+TsdYIhmQ+FDu41t8=
=qj1H
-----END PGP SIGNATURE-----