Hi everyone,I am having a problem that at first sight seems like an extremely simple problem to solve, yet I just can't figure it out.What I want to do, is update multiple rows of ONE column in one table. however, the value that is going to be inserted in this column needs to be retrieved from another table, which shares a key with the first table.This is the code you'll need to create the tables and data for this:CREATE TABLE SalesOrderDet (salesOrderDetId int NOT NULL PRIMARY KEY,purchaseOrderDetId int NOT NULL,quantityReceived int NULL)CREATE TABLE WarehouseProductEntryDet(WarehouseProductEntryDetId int NOT NULL PRIMARY KEY,purchaseOrderDetId int NOT NULL,quantityReceived int NOT NULL)INSERT INTO WarehouseProductEntryDet (WarehouseProductEntryDetId, purchaseOrderDetId, quantityReceived) VALUES (1,100,16)INSERT INTO WarehouseProductEntryDet (WarehouseProductEntryDetId, purchaseOrderDetId, quantityReceived) VALUES (2,101,12)INSERT INTO WarehouseProductEntryDet (WarehouseProductEntryDetId, purchaseOrderDetId, quantityReceived) VALUES (3,102,15)INSERT INTO SalesOrderDet (salesOrderDetId, purchaseOrderDetId, quantityReceived) VALUES (1,100,NULL)INSERT INTO SalesOrderDet (salesOrderDetId, purchaseOrderDetId, quantityReceived) VALUES (2,101,NULL)INSERT INTO SalesOrderDet (salesOrderDetId, purchaseOrderDetId, quantityReceived) VALUES (3,102,NULL)SELECT * FROM WarehouseProductEntryDetSELECT * FROM SalesOrderDet
What I want to do with the above, is simply grab the value stored in WarehouseProductEntryDet.quantityReceived, and copy it into SalesOrderDet.quantityReceived, using purchaseOrderDetId as the relationship between the two.These are the queries that I tried to use, including the error that each one returned (four separate attempts):UPDATE SalesOrderDet b, WarehouseProductEntryDet a SETb.quantityReceived = a.quantityReceived WHERE b.purchaseOrderDetId = a.purchaseOrderDetId AND b.quantityReceived IS NULL--Msg 102, Level 15, State 1, Line 1--Incorrect syntax near 'b'.SELECT (UPDATE SalesOrderDet b SETb.quantityReceived = a.quantityReceived WHERE b.purchaseOrderDetId = a.purchaseOrderDetId AND b.quantityReceived IS NULL) FROM WarehouseProductEntryDet a--Msg 156, Level 15, State 1, Line 1--Incorrect syntax near the keyword 'UPDATE'.UPDATE SalesOrderDet SET quantityReceived = (SELECT a.quantityReceived FROM WarehouseProductEntryDet a WHERE a.purchaseOrderDetId = purchaseOrderDetId) WHERE purchaseOrderDetId = a.purchaseOrderDetId AND quantityReceived IS NULL--Msg 4104, Level 16, State 1, Line 2--The multi-part identifier "a.purchaseOrderDetId" could not be bound.UPDATE SalesOrderDet SET quantityReceived = (SELECT a.quantityReceived FROM WarehouseProductEntryDet a WHERE a.purchaseOrderDetId = purchaseOrderDetId) WHERE purchaseOrderDetId = (SELECT a.quantityReceived FROM WarehouseProductEntryDet a WHERE a.purchaseOrderDetId = purchaseOrderDetId)AND quantityReceived IS NULL--Msg 512, Level 16, State 1, Line 1--Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.--The statement has been terminated.
I've been trying to avoid using a virtual table for this operation, hoping that there was something much more simple out there. I don't know if any of my UPDATEs above are even valid SQL.By the way, I am using SQL Server 2008 R2 EE.I would really appreciate help. Thanks guys and gals!
update multiple rows of one column using 2 tables
Posted - 2012-07-12 : 02:51:49
