August 15, 2010 at 11:39 pm
Dear experts,
I have the following query which generates for me row with identical entries :-
SELECT (T2.U_Bin) FROM OIBT T2, [@BINMATRIX] T3 WHERE T2.Quantity > 0 AND
T2.U_Bin = T3.Code GROUP BY T2.U_Bin HAVING COUNT(T2.U_Bin) >1
What I would like to do is for every row out put from the query above i make an update as follows :
IF (SELECT (T2.U_Bin) FROM OIBT T2, [@BINMATRIX] T3 WHERE T2.Quantity > 0 AND
T2.U_Bin = T3.Code GROUP BY T2.U_Bin HAVING COUNT(T2.U_Bin) >1)
BEGIN
DECLARE @TOTAL AS DECIMAL
SELECT @TOTAL = SUM (T2.QUANTITY) FROM OIBT T2,[@BINMATRIX] T3 WHERE T2.Quantity > 0 AND
T2.U_Bin = T3.Code GROUP BY T2.U_Bin HAVING COUNT(T2.U_Bin) >1
UPDATE [@BINMATRIX] SET U_CurrentWeight = @TOTAL,Code = OIBT.U_Bin,U_ItemCode = OIBT.ItemCode,
U_Empty = 'N',U_MaxWeight = OITM.U_MaxWeight,U_BatchNumber = OIBT.IntrSerial FROM OIBT,[@BINMATRIX],OITM WHERE OIBT.Quantity > 0 AND [@BINMATRIX].Code = OIBT.U_Bin AND OIBT.ItemCode = OITM.ItemCode
END
The update does not occur for every row, but only picks the total sum of the last row only and then updates the table [@binmatrix] with only that figure. It does not loop and make an update for each total for each row but for the last total only.
I would like it to make an update line by line for each row total . Please help.
August 16, 2010 at 12:04 am
Instead of an IF statement , should I make use of a while loop ?
August 16, 2010 at 12:28 am
I think there isn't required to user While loop, you just need to remove the [@BINMATRIX] in update query from "FROM"
IF (SELECT (T2.U_Bin) FROM OIBT T2, [@BINMATRIX] T3 WHERE T2.Quantity > 0 AND
T2.U_Bin = T3.Code GROUP BY T2.U_Bin HAVING COUNT(T2.U_Bin) >1)
BEGIN
DECLARE @TOTAL AS DECIMAL
SELECT @TOTAL = SUM (T2.QUANTITY) FROM OIBT T2,[@BINMATRIX] T3 WHERE T2.Quantity > 0 AND
T2.U_Bin = T3.Code GROUP BY T2.U_Bin HAVING COUNT(T2.U_Bin) >1
UPDATE [@BINMATRIX] SET U_CurrentWeight = @TOTAL,Code = OIBT.U_Bin,U_ItemCode = OIBT.ItemCode,
U_Empty = 'N',U_MaxWeight = OITM.U_MaxWeight,U_BatchNumber = OIBT.IntrSerial FROM OIBT,OITM WHERE OIBT.Quantity > 0 AND [@BINMATRIX].Code = OIBT.U_Bin AND OIBT.ItemCode = OITM.ItemCode
END
Try out this...
August 16, 2010 at 1:20 am
Martin, I am not able to figure out what these tables are but I will try to post an answer.
Having to loop for each row is a very costly operation. Once the application scales up and say, you get a million rows to loop through; this can lead to very bad performance. I think you can do the same by writing it in a single query.
What you are doing currently is -
if ( select ... having count(col) > 1)
begin
select @stuff = stuff from table
update some other stuff = @stuff
end
Instead try this -
update some other stuff = stuff
from table
--check up update with join
where
(select .. having count(col) > 1)
You can avoid the loops and get your query running faster. Further, try to use joins in your queries. Also, check out update with join.
http://msdn.microsoft.com/en-us/library/ms191472.aspx
It always helps if you post the table definition.
- arjun
https://sqlroadie.com/
August 16, 2010 at 2:07 am
Dear Hardik,
I have tried outputting @TOTAL and I only get one value, the last row. The update hence is made for that row only. Please find attached the two tables attached you may run the query on them.
You may leave out the OITM table.
Kind Regards
August 16, 2010 at 2:27 am
Arjun,
Thanks for this. In the update statement @total is actually a summation of the quantity field, how can we capture that in one update statement ?
Find attached the files for the two tables.
August 16, 2010 at 2:38 am
if possible send me some data.. which cause you problem...
(data with expected output)
and also the "OITM" structure for this...
August 16, 2010 at 2:52 am
Find attached TXT files with the actual data. Does this suffice for an import ? If not please tell me what to do.
August 16, 2010 at 3:00 am
Hi martin, thanks for the details. I will check them and get back to you. I'm a little busy currently.
- arjun
https://sqlroadie.com/
August 16, 2010 at 3:25 am
Hardik,
The OITM table is not that important in this. You may actually leave it out.
Kind Regards,
August 16, 2010 at 3:44 am
Try out this...
UPDATE [@BINMATRIX] SET U_CurrentWeight =
(SELECT SUM (CAST(T2.Quantity as numeric)) FROM OIBT T2,[@BINMATRIX] T3 WHERE CAST(T2.Quantity as numeric) > 0 AND
T2.U_Bin = T3.Code AND T2.U_Bin = OIBT.U_Bin)
,Code = OIBT.U_Bin,U_ItemCode = OIBT.ItemCode,
U_Empty = 'N', U_BatchNumber = OIBT.IntrSerial
FROM OIBT,[@BINMATRIX] WHERE CAST(OIBT.Quantity as numeric) > 0
AND [@BINMATRIX].Code = OIBT.U_Bin
You can put extra condition at bottom
August 16, 2010 at 5:55 am
Is that working....???
August 17, 2010 at 5:56 am
hi martin, why don't you do something like this?
UPDATE [@BINMATRIX] SET U_CurrentWeight = @TOTAL,Code = OIBT.U_Bin,U_ItemCode = OIBT.ItemCode,
U_Empty = 'N',
--U_MaxWeight = OITM.U_MaxWeight,
U_BatchNumber = OIBT.IntrSerial
FROM
OIBT,[@BINMATRIX]--,OITM
WHERE
OIBT.Quantity > 0 AND [@BINMATRIX].Code = OIBT.U_Bin --AND OIBT.ItemCode = OITM.ItemCode
and OIBT.U_Bin in
(
SELECT (T2.U_Bin)
FROM OIBT T2, [@BINMATRIX] T3
WHERE T2.Quantity > 0
AND T2.U_Bin = T3.Code
GROUP BY T2.U_Bin
HAVING COUNT(T2.U_Bin) >1
)
You can write a sub-query for the @total part.
I haven't verified this with data as I didn't want to copy the data you posted and create insert scripts.
- arjun
https://sqlroadie.com/
August 30, 2010 at 7:38 am
Dear Hardik,
Sorry for the late reply. Thanks a lot for your query, it worked well.
Kind Regards,
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply