Thread: update entire table (with PostGreSQL alone)?

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?

Lots of thanks for any help anyone can give me,

Wiliam Knobbel, PhD-Student

On Fri, Aug 22, 2003 at 10:48:39 +0200,
  yruhn <yruhn@xdh.nl> wrote:
> Dear anyone,
>
> 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
notpresent (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?

Use INSERT and DELETE queries. To get a more specific answer you
need to state what you want to do more precisely.

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.l.johnson@cox.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