Re: Insert Question
От | Michael Glaesemann |
---|---|
Тема | Re: Insert Question |
Дата | |
Msg-id | 0AF5E866-7009-498B-ADDC-1427B82ACDDA@seespotcode.net обсуждение исходный текст |
Ответ на | Insert Question (Phillip Nelson <phillip.nelson@ints.com>) |
Список | pgsql-novice |
On Jun 26, 2007, at 11:39 , Phillip Nelson wrote: > 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. > 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? When two tables are JOINed, the result includes only rows for which the JOIN condition is true. As you've discovered, when joining ivmf_tmp to ivfmx_tmp USING (id), there will be no rows returned from ivfm unless there are corresponding rows in ivfmx_tmp (and vice versa). Note I'm using USING (id) rather than ON (ivfm_tmp.id = ivfmx_tmp.id) which has the nice property of returning only one id column rather than two (one for each ivfm_tmp and ivfmx_tmp). What you're looking for is a LEFT JOIN. For A LEFT JOIN B, all rows of A will be returned. Corresponding rows in B will also be returned, and when there are no corresponding rows in B, NULL will be returned in place of B's columns. So, we've got: INSERT into inventory_data (id, cat, desc, seq, type, bcode1, comm1, comm2, comm3) 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 LEFT JOIN ivfmx_tmp USING (id) WHERE ivfm_tmp.id NOT IN ( SELECT inventory_data.id FROM inventory_data); It's a good habit to list the columns in an INSERT statement as I've done here. This helps insulate the INSERT statement from DDL that may occur on the table you're inserting into as well as being self- documenting. Now, you want to insert 0.00 when comm1, comm2, and comm3 when there's no corresponding rows in ivfmx_tmp -- in otherwords, when comm1, comm2, and comm3 are NULL. The COALESCE function can help you out here. COALESCE returns its first argument which is not NULL. INSERT into inventory_data (id, cat, desc, seq, type, bcode1, comm1, comm2, comm3) SELECT ivfm_tmp.id , ivfm_tmp.cat , ivfm_tmp.desc , ivfm_tmp.seq , ivfm_tmp.type , ivfm_tmp.bcode1 , COALESCE(ivfmx_tmp.comm1, 0.00) as comm1 , COALESCE(ivfmx_tmp.comm2, 0.00) as comm2 , COALESCE(ivfmx_tmp.comm3, 0.00) as comm3 FROM ivfm_tmp LEFT JOIN ivfmx_tmp USING (id) WHERE ivfm_tmp.id NOT IN ( SELECT inventory_data.id FROM inventory_data); I think you could possibly also handle this with DEFAULT 0.00 on the comm1, comm2, and comm3 columns. As an aside, column names such as comm1, comm2, and comm3 are sometimes a sign that a table should be refactored into two associated with a foreign key. Without more details about your schema, it's hard to say if that's the case here, but it may be something you want to look into. Hope this helps. Michael Glaesemann grzm seespotcode net
В списке pgsql-novice по дате отправления: