Use the MERGE statement to select rows from one or more sources for update or insertion into one or more tables. You can specify conditions to determine whether to update or insert into the target tables.This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.MERGE:
MERGE INTO tgt USING src ON (src.object_id = tgt.object_id) WHEN MATCHED THEN UPDATE SET tgt.object_name = LOWER(src.object_name) WHERE src.object_type = 'PACKAGE' DELETE WHERE tgt.object_type = 'TYPE' WHEN NOT MATCHED THEN INSERT (tgt.object_id, tgt.object_name) VALUES (src.object_id, src.object_name) WHERE src.created > TRUNC(SYSDATE) - 365; MERGE INTO anag_bilance old USING (SELECT 93 cd_deposito FROM dual) new ON (old.cd_deposito = new.cd_deposito) WHEN MATCHED THEN UPDATE SET op_mod = 'sigo2' WHERE nr_ip_bilancia = '10.62.2.241' WHEN NOT MATCHED THEN INSERT (cd_deposito, cd_postazione, nr_ip_bilancia, ds_bilancia, nr_porta, fl_attiva, fl_bizerba_st, fl_default, dt_ins, op_mod) VALUES (95, 1, '10.62.2.243', 'Prova', 1025, 'S', 'S', null, null, 'sigo'); MERGE WITH DUAL: MERGE INTO of_ordini_fornitori orfo USING DUAL ON (orfo.cd_deposito = p_CD_DEPOSITO AND orfo.dt_consegna = v_DT_ULTIMA_CONSEGNA AND orfo.cd_fornitore = PROLIS.CD_FORNITORE AND orfo.nr_promozione = DISTR.NR_PROMOZIONE AND orfo.cd_int = LISTA.CD_INT AND orfo.vl_prezzatura = LISTA.VL_PREZZATURA) WHEN MATCHED THEN UPDATE SET ps_variazione_ordine = ps_variazione_ordine + v_FQTA, dt_agg = SYSDATE, op_mod = v_OPERATORE WHEN NOT MATCHED THEN INSERT (cd_deposito, dt_consegna, cd_fornitore, nr_promozione, cd_int, cd_origine, cd_varieta, vl_costo, vl_costo_iper, vl_prezzatura, ps_ordine, ps_variazione_ordine, dt_trasmissione, dt_ins, dt_agg, op_mod) VALUES (p_CD_DEPOSITO, v_DT_ULTIMA_CONSEGNA, PROLIS.CD_FORNITORE, DISTR.NR_PROMOZIONE, LISTA.CD_INT,PROLIS.CD_ORIGINE, PROLIS.CD_VARIETA, PROLIS.VL_COSTO, PROLIS.VL_COSTO_IPER, LISTA.VL_PREZZATURA, 0, v_FQTA, NULL, SYSDATE, NULL, v_OPERATORE); |