Update Query Asssitance Please
Update Query Asssitance Please
(OP)
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_id3
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?
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