How i can update fields in a from statement?

Question

UPDATE
    RU
SET
    RU.Carrier = E1.TCarrier,
    RU.Carrier_Tracking_Number = E4.Bill_Ladin
FROM Rec_Unit RU
INNER JOIN Exp02 E2 ON (E2.Coment1 = RU.Pallet_ID)
INNER JOIN Exp05 E5 ON (E5.No_Packing = E2.No_Packing)
INNER JOIN Exp04 E4 ON (E4.No_Factura = E5.No_Factura)
INNER JOIN Exp01 E1 ON (E1.No_Packing = E2.No_Packing)
WHERE RU.Event = 'RESULT'
AND RU.Carrier IS NULL
AND RU.Carrier_Tracking_Number IS NULL
AND E1.TCarrier IS NOT NULL
AND E4.Bill_ladin IS NOT NULL

I am trying to update the table Rec_Unit with values from other tables but the statement doesn't work, I use the syntax from this post How do I UPDATE from a SELECT in SQL Server?

This is the SQL error:

SQL Error: ORA-00933: SQL command not properly ended

Oracle doesn't support `join` in `update`. You are using SQL Server syntax, hence the error. — Gordon Linoff, Nov 30 '15 at 16:16
oh really? I not want to do all this work manually, thank you @Gordon Linoff — Alfonso Jaquez, Nov 30 '15 at 16:21

score 3 · Accepted Answer · answered Nov 30 '15 at 16:23

I'd use a merge statement instead. Something like the following, perhaps:

merge into rec_unit tgt
using (select ru.primary_key_col, -- amend the name of the primary key column(s) as appropriate
              e1.tcarrier,
              e4.bill_ladin
       FROM   Rec_Unit RU
              INNER JOIN Exp02 E2 ON (E2.Coment1 = RU.Pallet_ID)
              INNER JOIN Exp05 E5 ON (E5.No_Packing = E2.No_Packing)
              INNER JOIN Exp04 E4 ON (E4.No_Factura = E5.No_Factura)
              INNER JOIN Exp01 E1 ON (E1.No_Packing = E2.No_Packing)
       WHERE  RU.Event = 'RESULT'
       AND    RU.Carrier IS NULL
       AND    RU.Carrier_Tracking_Number IS NULL
       AND    E1.TCarrier IS NOT NULL
       AND    E4.Bill_ladin IS NOT NULL) src
  on (tgt.primary_key_col = src.primary_key_col) -- amend the name of the primary key column(s) as appropriate
when matched then
update set tgt.carrier = src.tcarrier,
           tgt.carrier_tracking_number = src.bill_ladin;

How i can update fields in a from statement?

1 Answers1