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);