This query works to return a result set against two tables. I am locating records that have not sold in some time. Within the inv table there is a field called inv_user4 which currently isn't being used as an active part of the inventory record. I would like to place a static value here that I define.
I have only ever been able to figure out an update against a single table used in the WHERE clause. The statement below fails to work for me. How can I use the select statement above to add TEST to the inv.inv_user4 field?
Thank you for your consideration in this matter.
IBM Informix Dynamic Server Version 10.00.UC5W5
Code:
SELECT item_stores.store_id
, inv.inv_id3
, inv.inv_desc
,(inv.inv_tot1+inv.inv_tot2+inv.inv_tot3+inv.inv_tot4+inv.inv_tot5+inv.inv_tot6+inv.inv_tot7+inv.inv_tot8+inv.inv_tot9+inv.inv_tot10+inv.inv_tot11+inv.inv_tot12+inv.inv_tot13+inv.inv_tot14+inv.inv_tot15+inv.inv_tot16+inv.inv_tot17+inv.inv_tot18+inv.inv_tot19+inv.inv_tot20+inv.inv_tot21+inv.inv_tot22+inv.inv_tot23+inv.inv_tot24) as tpaas
, inv.inv_last_pur
, inv.inv_last_sale
, inv.inv_sold_mtd
, inv.inv_sold_ytd
, inv.inv_sold_ltd
FROM item_stores
, inv
WHERE ((inv_tot1+inv_tot2+inv_tot3+inv_tot4+inv_tot5+inv_tot6+inv_tot7+inv_tot8+inv_tot9+inv_tot10+inv_tot11+
inv_tot12+inv_tot13+inv_tot14+inv_tot15+inv_tot16+inv_tot17+inv_tot18+inv_tot19+inv_tot20+inv_tot21+
inv_tot22+inv_tot23+inv_tot24) < "3")
AND ((inv_tot1+inv_tot2+inv_tot3+inv_tot4+inv_tot5+inv_tot6+inv_tot7+inv_tot8+inv_tot9+inv_tot10+inv_tot11+
inv_tot12+inv_tot13+inv_tot14+inv_tot15+inv_tot16+inv_tot17+inv_tot18+inv_tot19+inv_tot20+inv_tot21+
inv_tot22+inv_tot23+inv_tot24) > "0")
AND item_stores.on_hand_qty > '0'
-- AND item_stores.store_id != "009"
AND item_stores.store_id = "012"
AND inv.inv_last_sale < DATETIME (2010-01-01) YEAR TO DAY
AND item_stores.item_id = inv.inv_id3I have only ever been able to figure out an update against a single table used in the WHERE clause. The statement below fails to work for me. How can I use the select statement above to add TEST to the inv.inv_user4 field?
Code:
UPDATE inv
SET inv.inv_user4 = 'TEST', inv.inv_upd_flag = 'Y'
WHERE inv.inv_id3 IN
(SELECT inv.inv_id3
FROM item_stores
, inv
WHERE ((inv_tot1+inv_tot2+inv_tot3+inv_tot4+inv_tot5+inv_tot6+inv_tot7+inv_tot8+inv_tot9+inv_tot10+inv_tot11+
inv_tot12+inv_tot13+inv_tot14+inv_tot15+inv_tot16+inv_tot17+inv_tot18+inv_tot19+inv_tot20+inv_tot21+
inv_tot22+inv_tot23+inv_tot24) < "3")
AND ((inv_tot1+inv_tot2+inv_tot3+inv_tot4+inv_tot5+inv_tot6+inv_tot7+inv_tot8+inv_tot9+inv_tot10+inv_tot11+
inv_tot12+inv_tot13+inv_tot14+inv_tot15+inv_tot16+inv_tot17+inv_tot18+inv_tot19+inv_tot20+inv_tot21+
inv_tot22+inv_tot23+inv_tot24) > "0")
AND item_stores.on_hand_qty > '0'
AND item_stores.store_id != "009"
AND inv.inv_last_sale < DATETIME (2010-01-01) YEAR TO DAY
AND item_stores.item_id = inv.inv_id3)Thank you for your consideration in this matter.
Code:
select first 1 dbinfo("version", "full") from systables;IBM Informix Dynamic Server Version 10.00.UC5W5