Informix UPDATE syntax

[craig322]

I have an SQL that I ran on our teradata and Oracle databases and was wondering what the syntax would be for informix. I get an syntax error on informix that seems to be from my using the FROM statement. Here is the SQL I am trying to run.

UPDATE e
FROM dsx_tables.inventory_history i, dss_tables.e_inventory_history e
SET qty_on_hand = i.qty_on_hand,
qty_on_order = i.qty_on_order,
qty_on_backorder = i.qty_on_backorder,
qty_on_reserve = i.qty_on_reserve,
unit_cost = i.unit_cost
WHERE e.date_loaded = i.date_loaded
AND e.branch_id = i.branch_id
AND e.sim_mfr_no = i.sim_mfr_no
AND e.sim_item_no = i.sim_item_no
AND date_loaded in ('01/06/01','01/05/01')

TIA

Craig

 

[AdrianRutter]

Surely you have failed to state the table and column

You should surely use

UPDATE e
FROM dsx_tables.inventory_history i, dss_tables.e_inventory_history e
SET e.qty_on_hand = i.qty_on_hand,
e.qty_on_order = i.qty_on_order,
e.qty_on_backorder = i.qty_on_backorder,
e.qty_on_reserve = i.qty_on_reserve,
e.unit_cost = i.unit_cost
WHERE e.date_loaded = i.date_loaded
AND e.branch_id = i.branch_id
AND e.sim_mfr_no = i.sim_mfr_no
AND e.sim_item_no = i.sim_item_no
AND i.date_loaded in ('01/06/01','01/05/01')

Notice I have used i. for date_loaded at the end, as I don't know which table you want to limit this for...
I have added e. at the beginning of each SET statement...!!!

Let me know if this works.


Adrian Adrian Rutter
Consultant - UK