Re: update entire table (with PostGreSQL alone)?

On Fri, 2003-08-22 at 03:48, yruhn wrote:
> Dear anyone,
>
> I wish to update an entire table with just using (PostGre)SQL
> (preferably without resorting to C, Python or so). Can I? If
> so how?
>
> For example, I want to update/replace table:
>
> a | b | c
> ---+---+---
> 1 | 2 | 3
> 4 | 5 | 6
> 7 | 8 | 9
>
> with table:
>
> a | b | c
> ---+---+---
> 1 | 2 | 2
> 4 | 5 | 6
> 9 | 0 | 0
>
> So, I wish to:
> 1- update every data point in the table (based on the UNIQUE
> column 'a' (=PRIMARY KEY)), and if this value of a is not
> present (such as for instance value 9: 9,0,0),I wish to;
> 2- insert a new row with data points
> 3- and I want to delete old rows that are not used present
> in the new table (such as row 7 : 7,8,9)
>
> How can I update an entire table with just using (PestGre)SQL?

I think this will do what you want. At the end, table_1 will
look like table_2...

create table table_1 (
a integer primary key,
b integer,
c integer);

create table table_2 (
a integer primary key,
b integer,
c integer);

insert into table_1 values (1, 2, 3);
insert into table_1 values (4, 5, 6);
insert into table_1 values (7, 8, 9);

insert into table_2 values (1, 2, 3);
insert into table_2 values (4, 5, 6);
insert into table_2 values (9, 0, 0);

delete from table_1
where not exists (select *
from table_2
where table_2.a = table_1.a);

insert into table_1
select *
from table_2
where a not in (select t1.a
from table_1 t1,
table_2 t2
where t2.a = t1.a);

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

"You ask us the same question every day, and we give you the
same answer every day. Someday, we hope that you will believe us..."
U.S. Secretary of Defense Donald Rumsfeld, to a reporter