Skip site navigation (1) Skip section navigation (2)

update or insert into table from a view

From: Phillip Nelson <phillip(dot)nelson(at)ints(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: update or insert into table from a view
Date: 2007-06-06 03:53:43
Message-ID: 46662FC7.1090704@ints.com (view raw or flat)
Thread:
Lists: pgsql-novice
Any help would be greatly appreciated. I am basically attempting to 
merge a view into a table. I have been successful at inserting all rows 
from the view that do not exist in the table. However, I cannot figure 
out how to update the existing rows in the table with those from the 
view by primary key. Currently I am doing the following:

insert into statistic_descriptions_data
select seq, "desc", (CASE WHEN epr_flag='Y' THEN TRUE ELSE FALSE END),
(CASE WHEN csr_flag='Y' THEN TRUE ELSE FALSE END), (CASE WHEN
sr_flag='Y' THEN TRUE ELSE FALSE END), (CASE WHEN oil_flag='Y' THEN TRUE
ELSE FALSE END), (CASE WHEN jli_flag='Y' THEN TRUE ELSE FALSE END),
(CASE WHEN msr_flag='Y' THEN TRUE ELSE FALSE END) from ivfs where seq not in
                 (select "sequence" from statistic_descriptions_data);

The table columns are setup as bool where the view is setup with bpchar 
for all the flag fields. Thus the case statements to get around cast 
problems. Do I need to create a function(), trigger, or something else 
to accomplish this. Basically if seq from the view = "sequence" in the 
table I need to update that row instead of inserting.
Thanks in advance.

pgsql-novice by date

Next:From: Andrej Ricnik-BayDate: 2007-06-06 04:03:14
Subject: Re: Installation of postgresql database as root
Previous:From: Ali, LuqmanDate: 2007-06-06 03:38:50
Subject: Re: Installation of postgresql database as root

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group