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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrej Ricnik-Bay | 2007-06-06 04:03:14 | Re: Installation of postgresql database as root |
Previous Message | Ali, Luqman | 2007-06-06 03:38:50 | Re: Installation of postgresql database as root |