Update one table with data from a 2nd table

Hello,

I'm pretty new to DB2 SQL programming, and I've been banging my head for 2 weeks now trying to figure out the following situation. Here is an example of what i'm trying to do:

I have the 2 tables (Orders and Temp_Orders):

-------- --------------
ORDERS TEMP_ORDERS
-------- --------------
StoreID OID Order_Name StoreID OID Order_Name
------- --- ------------ ------- --- ------------
777 111 -- 777 111 Order1
888 111 -- 888 111 Order2
999 111 -- 999 111 Order2
222 516 -- 222 727 Order9

So i'm trying to update all of the values in the base table from this temp table, but only for the items with a OID of 111. This is the query that i'm trying to use (but it always fails since I have more than one row coming back from the temp table):

UPDATE Orders
SET Order_Name = ( SELECT Order_Name FROM Temp_Orders WHERE OID = 111)
WHERE OID = 111;

The subquery returns the data that I need to transfer over, but since its more than one row, the update query always fails for me. Does anyone know of a better way to do this? Oh, and I'm using DB2 version 7. Any help would be greatly appreciated.

Thanks!

As far as I know, you have a couple of options.

1) Either delete all rows in TEMP_ORDERS with the OID of 111, and then insert them using this:

DELETE from TEMP_ORDERS where OID = 111;
INSERT INTO TEMP_ORDERS (StoreID, OID, Oder_Name) select * from ORDERS where OID = 111;

2) in whatever programming language you are using, select all rows from ORDERS with oid-111, loop over them, and for each row, update temp_orders.order_name where orders.oid = temp_orders.oid and orders.store_id = temp_orders.store_id

First, Thank you very much for your reply.

However, I see after reading your reply that I left off a little bit of important information. These tables have referential integrity to other tables, so if I would not be allowed to delete the rows in the Orders table (in order to copy in new data from temp_orders) because of the rows of data in the other associated tables. So I need to be able to perform an update to the orders table, and not a delete and insert as you suggested.

Do you see any other potential solutions to this issue?

Thank You.

The second solution should work for you. Are you using .NET? Select all the rows from ORDERS where OID=111. Then loop through the result set and for each row, update the corresponding row in TEMP_ORDERS where the OID and store_id match. You're really only changing the order_name, right?

Yes, I'm using .Net. I will try to see if I can implement this from the .Net code (without using embedded SQL). My only concern is the fact that this update process has to be transactional since there are 2 other processes that must occur. There is one insert into some other table that occurs before the UPdate of the Orders table, and a delete process which must occur after this update of the Orders table. These 2 other processes are already implemented in a single stored proc which functions correctly. I was just trying to add this update process in the middle to the same stored proc so that keeping this entire process transactional would be a little easier.