ORACLE MERGE CLAUSE (EX1)

MERGE INTO sigo_magmov m
USING (select l.vl_costo costo_lotto, m.rowid fede
         from sigo_magmov m
        inner join sigo_lotti l
           on m.nr_lotto = l.nr_lotto
        where cd_causale = 76
          and m.dt_tem >= '20120101'
          and not m.nr_lotto is null
          and m.nr_lotto = 73840) magmov_lotti
ON (m.rowid = magmov_lotti.fede)
WHEN MATCHED THEN
  UPDATE SET vl_costo = costo_lotto;

* sia sigo_magmov che sigo_lotti devono avere una chiave primaria

ORACLE MERGE CLAUSE (EX2)

MERGE INTO sigo_magmov mv
USING (SELECT *
         FROM sigo_movmerci
        WHERE dt_tem = '20120401'
          AND cd_causale = 7
          AND cd_int in ('0434700733', '0434602958')
          AND nr_tem = '95024198') mm
   ON (mv.cd_deposito = mm.cd_deposito 
  AND mv.cd_causale = mm.cd_causale 
  AND mv.dt_tem = mm.dt_tem 
  AND mv.nr_tem = mm.nr_tem 
  AND mv.cd_int = mm.cd_int)
 WHEN MATCHED THEN
  UPDATE SET mv.ps_movimento = mm.ps_netto, mv.vl_costo = mm.vl_costo
 WHEN NOT MATCHED THEN
  INSERT VALUES
    (mm.cd_deposito, mm.dt_movimento, mm.dt_tem, mm.nr_tem, 
     mm.dt_ddt, mm.nr_ddt, mm.cd_int, mm.dt_ordine_pdv, 
     mm.nr_ordine_pdv, mm.cd_causale, mm.cd_clifor, 
     mm.nr_colli, mm.ps_netto, null, mm.vl_costo_iper, 
     round(mm.ps_netto * mm.vl_costo_iper, 2), 0, mm.vl_costo, 
     0, null, null, null, null, 0, 0, null, 'N', 'N', null, 'N', 
     null, null, null, null, 0, null, sysdate, null, null, null, 
     null, mm.dt_agg, mm.nr_lotto, mm.cd_clifor, null, null, null, null);

ORACLE WITH CLAUSE

INSERT INTO (COLUMN LIST)
WITH ALIAS NAME AS (SELECT SUB QUERY) SELECT FINAL QUERY;

ORACLE INNER JOIN (EX1)

UPDATE (SELECT mgi_descr3.visibile, ‘N’as new_val
         FROM mgi_vocicontabili
        INNER JOIN mgi_descr3 ON mgi_descr3.cd_soc = mgi_vocicontabili.cd_soc
          AND mgi_descr3.cd_pdv = mgi_vocicontabili.cd_pdv
          AND mgi_descr3.cd_cont1 = mgi_vocicontabili.cd_voce_cont
        WHERE mgi_vocicontabili.cd_sap = ’0089000000′
          AND mgi_descr3.visibile = ‘S’) SET visibile = new_val

ORACLE INNER JOIN (EX2)

UPDATE cp_mn_voci a 
   SET a.fl_visibile = ‘S’ 
 WHERE a.cd_gruppo IN (SELECT b.cd_gruppo 
                         FROM cp_mn_validaz b 
                        WHERE b.cd_att = 102 AND b.cd_op = ‘supporto’) 
   AND a.cd_voce = ’01′ 
   AND a.DS_VOCE like‘%d%’ 
   AND a.CD_VOCE = ’01′

ORACLE INNER JOIN (EX3)

UPDATE SIGO_CONCENTRATORE
   SET FL_BLOCCO=’S’
 WHERE CD_DEPOSITO = 95
   AND DT_CONSEGNA = ’20110115′
   AND NR_GIRO = 1
   AND (SELECT v.CD_GRM 
          FROM V_REFERENZE v 
         WHERE v.CD_INT = SIGO_CONCENTRATORE.CD_INT) in (40,51,56,53)

SQLSERVER INNER JOIN (EX1)

UPDATE (TABLEA AS A INNER JOIN TABLEB AS B     
                 ON A.F1 = B.F1)
        SET A.X = 1, B.Y = 2 
      WHERE A.Z = 10

http://psoug.org/reference/update.html (oracle)
http://stackoverflow.com/questions/230196/using-a-subquery-instead-of-a-table-name-in-an-oracle-update-statement (oracle)
http://www.tek-tips.com/faqs.cfm?fid=1958 (sql server)