From: | Phillip Nelson <phillip(dot)nelson(at)ints(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Insert Question |
Date: | 2007-06-26 16:39:22 |
Message-ID: | 4681413A.4020804@ints.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have an ivfm_tmp table and am trying to insert all records from that
table into inventory_data if they do not already exist. Furthermore, I
need to join commissions from ivfmx_tmp table if there are existing
records for that id.
This insert statement works fine as long as their is an associated
ivfmx_tmp record to join. If there is no ivfmx_tmp record to join the
record is not inserted and there doesn't seem to be any errors.
Ultimately I need to default the ivfmx_tmp.comm1, comm2, and comm3
fields to 0.00 if there is no join found and insert the record into
inventory_data. Is this possible?
INSERT into inventory_data select ivfm_tmp.id, ivfm_tmp.cat,
ivfm_tmp.desc, ivfm_tmp.seq, ivfm_tmp.type, ivfm_tmp.bcode1,
ivfmx_tmp.comm1, ivfmx_tmp.comm2, ivfmx_tmp.comm3
from ivfm_tmp INNER JOIN ivfmx_tmp
ON ivfm_tmp.id = ivfmx_tmp.id where ivfm_tmp.id not in
(select inventory_data.id from inventory_data);
From | Date | Subject | |
---|---|---|---|
Next Message | David Gardner | 2007-06-26 17:03:04 | parameters to pl/pgSQL functions |
Previous Message | Fernando Hevia | 2007-06-26 16:15:16 | Re: NO DATA FOUND Exception |