Correlated Sub Query... in reverse?

[swisstoni]

Hey guys,

I've got the following SQL:

PARAMETERS orderno Text ( 255 );
 
UPDATE tblOrder_Item SET item_qty = I.item_shelfstock - Q.pre_allocated_stock
 
WHERE orderitem_id IN 
(
 
SELECT Q.orderitem_id FROM qryOrder_Item AS Q INNER JOIN tblItem AS I ON Q.item_id=I.item_id
 
WHERE
 
(I.item_shelfstock - Q.pre_allocated_stock) < Q.item_qty
 
AND
 
(I.item_shelfstock - Q.pre_allocated_stock) >= 0
 
AND Q.order_id=[orderno]  );

But when I run this, Access prompts me for the values of I.item_shelfstock - Q.pre_allocated_stock, I assume from the first line.

I realise this is probably because I and Q aren't defined until later in the query, but I'm unsure how to swap the layout round....

 

[neileg]

So why use the Q and I aliases anyway? Just use the full object names.

 

[swisstoni]

Well, item_shelfstock and pre_allocated_stock are in different tables/queries to tblOrder_Item, and qryOrder_Item isn't updatable because it uses SUM().

So I've changed it to the following:

PARAMETERS orderno Text ( 255 );
 
UPDATE
 
((tblOrder_Item INNER JOIN tblItem ON tblItem.item_id = tblOrder_Item.item_id) INNER JOIN qryOrder_Item ON 
qryOrder_Item.orderitem_id = tblOrder_Item.orderitem_id)

SET tblOrder_Item.item_qty = tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock
 
WHERE tblOrder_Item.orderitem_id IN 
 
( 
 
SELECT Q.orderitem_id FROM qryOrder_Item AS Q INNER JOIN tblItem AS I ON Q.item_id=I.item_id 
 
WHERE  
 
(I.item_shelfstock - Q.pre_allocated_stock) < Q.item_qty  
 
AND  
 
(I.item_shelfstock - Q.pre_allocated_stock) >= 0  AND Q.order_id=[orderno]  );

But that gives me the error "Operation must use an updateable query"...

 

[jal]

Well, item_shelfstock and pre_allocated_stock are in different tables/queries to tblOrder_Item, and qryOrder_Item isn't updatable because it uses SUM().

So I've changed it to the following:

PARAMETERS orderno Text ( 255 );
 
UPDATE
 
((tblOrder_Item INNER JOIN tblItem ON tblItem.item_id = tblOrder_Item.item_id) INNER JOIN qryOrder_Item ON 
qryOrder_Item.orderitem_id = tblOrder_Item.orderitem_id)

SET tblOrder_Item.item_qty = tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock
 
WHERE tblOrder_Item.orderitem_id IN 
 
( 
 
SELECT Q.orderitem_id FROM qryOrder_Item AS Q INNER JOIN tblItem AS I ON Q.item_id=I.item_id 
 
WHERE  
 
(I.item_shelfstock - Q.pre_allocated_stock) < Q.item_qty  
 
AND  
 
(I.item_shelfstock - Q.pre_allocated_stock) >= 0  AND Q.order_id=[orderno]  );

But that gives me the error "Operation must use an updateable query"...

The rule of thumb, as far as I know, is that if any of your queries contain a GROUP BY clause, Access won't let you do an UPDATE.

To solve this, you may have to output your JOIN into a temp table, and then use the temp table to UPDATE the desired table.

 

[neileg]

Yeah, what jal said.