Updating multiple rows 2

[imosri]

Hi, here is the scenario.

I need to update an inventory table.

The inventory table is like this:
Table M_Inventory
MI_Id int (key) incremental
MI_Sku varchar (8)
CAU_Location (foreign key from a table named CA_Location)
Other no relevant columns

There are 2000 SKUs for each location (3 of them) for 6,000 records and growing.

Now, there is a Ticket table, their relevant information for this operation is:
Table M_Detail_Ticket
MT_Id (Ticket number, foreign key from a table named M_Ticket)
SKU varchar (8)
MDT_Quantity
Other columns.

On average a ticket will have about 10 SKUs in it.

There is a stored procedure to "close" the ticket. This means change some flags on the M_Ticket, move total to a sales table and other stuff, this is all done and working fine. Easy to perform because the store procedure performs single record operations.

I am facing problem getting a solution for this multiple record update.

Your suggestion are appreciated.

Can I do this task on a single update statemnt?
No much experience on indexes outside primary key, can I implement a sku/location index in order to improve performance?

Thank you in advance.

 

[imosri]

I forgot yo mention that M_Detail_ticket does NOT have de CAU_Location column, as you can see is on the master ticket table (M_Ticket)

Regards,

 

[SQLSister]

Not sure we can answer this wihtout knowing what the single record update is actually doing. But probably yes it can be done in a set-based statment.

An index might help improve select performance, it would hurt insert, update or delete performance.

Questions about posting. See faq183-874

 

[donutman]

I agree with SQLSister, we need to know what updates you want to perform and how you will obtain the multiple tickets to close. It would probably help performance if your M_Detail_Ticket table had a primary key, especially if it was MT_Id and SKU. If a single SKU can be listed twice on a ticket, then you should have an identity column in that table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[imosri]

Hi,

This update statment is actually working, but to be honest I was lucky getting it to work. several 100's test show me that it is working properly.

update m_Iventory
set MI_Quantity=MI_Quantity +
(
select m_detail_ticket.mdt_quantity
where
m_inventory.sku=m_detail_ticket.sku
and
m_ticket.CAU_Id=m_inventario.CAU_Id
and
m_ticket.MT_Id=m_detail_ticket.MT_Id
and
m_detail_ticket.MT_Id=@MT_Id
and
m_ticket.CAU_id=@CAU_Id
)

from m_detail_ticket,M_Ticket where m_detail_ticket.MT_Id=@MT_Id
and
M_Ticket.CAU_Id=@CAU_id
and
m_inventory.sku=m_detail_ticket.sku
and
m_Inventory.CAU_Id=@CAU_Id
and
M_Ticket.MT_Id=@MT_Id
GO

It just seems the only way to get a 1 on 1 relation between the query and subquery in order to get the update performed.

I'll test sugestion for make an index for SKU/CAU_Id columns. The inventory never deletes a record and inserting is performed only when new products are added to the product catalog, about 50 a week, one by one. So losing performance on insert is not big issue here, performance on the update is a must, because we calculate to have about 300-400 operations of this type per day.

Thank you for your comments

 

[imosri]

Donutman,

You are rigth, I checked again.
There is a possibility to have the same SKU multiple times in a ticket.

The update statement is only updating partialy the inventory (the last sku).

100's of tests went to the trash!!!, thank you for pointing that.

How to fix that?

Can I sum() the mdt_quantity row in the above statement?

Regards,

 

[donutman]

imosri,
Try doing it this way.

UPDATE I
   SET MI_Quantity=MI_Quantity+D.mdt_Quantity  
   FROM m_Inventory I INNER JOIN m_Detail_Ticket D
      ON I.SKU=M.SKU INNER JOIN m_Ticket T
      ON T.MT_Id=D.MT_Id
   WHERE @CAU_Id=T.CAU AND @MT_Id=T.MT_Id

However, I question the approach that you are using for the following reasons:
Why is the inventory going up as tickets are closed...you would expect them to go down?
Updating a quantity field using this approach is asking for trouble. If an error is made it corrupts data possibly beyond your ability to fix it. It would be better to have a transaction table upon which a summation can be performed for each SKU as inventory is added and subtracted based upon a transaction date. Then the quantity field in the Inventory table can be updated based upon all transactions up to a certain date. That date also becomes an entry in the Inventory table. Periodically the application can warehouse old transactions and add a summary transaction value in the Transaction table for amounts warehoused.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[imosri]

Donutman,

I'll test your suggestion after this post. I will also update the results of the testing.

You are right about the inventory going up, let me clarify, there is an additional column located in M_Ticket named MT_Type, that hadles the type of transaction, basically is a factor (1 or -1) depending of the type of "ticket", so there is a sale the factor is -1, if there is a return or simply adding inventory to the store (here everything is a "Ticket"), then is a 1. I removed the factor from the exercise just to focus on the problem.

About looking for trouble, again, you are rigth, but this is a "Realtime" inventory. There is a table with the "seed" inventory at a specified date, with a initial inventory. Every day there is a stored procedure that populates this "Realtime" inventory with the seed and all the movement up to date. At the end of the day the main iventory table is carfully updated with commits and cheksums. If something wrong happens to the "realtime" M_Inventory table, it can be droped and restored in a minute without compromising the stability and quality of the main inventory table. I concede that even this approach is prone to error but I found this way safer than touching one sole inventory table, making it harder or even impossible to restore if something goes wrong.

Thank you for your code and more on your suggetions.

Regards,

 

[imosri]

donutman,

After several tries and test I finally got this to work.

Your comments are truly appreciated.

First I created a view that adds up same sku quantities into one row:

Sorry I change columns names (actually the system is being build in spanish), I did it for your clarity:

CREATE VIEW dbo.v_suma_ticket
AS
SELECT 
dbo.M_Ticket.MT_ID, 
dbo.M_Detalle_Ticket.SKU, 
SUM(dbo.M_Detalle_Ticket.MDT_Cantidad) AS MDT_Cantidad
FROM         
dbo.M_Ticket 
INNER JOIN
dbo.M_Detalle_Ticket ON dbo.M_Ticket.MT_ID = dbo.M_Detalle_Ticket.MT_ID
GROUP BY dbo.M_Ticket.MT_ID, dbo.M_Detalle_Ticket.SKU

Then using your suggestion I used the follwing update statment in my stored procedure:

set MI_Cantidad=Mi_Cantidad+d.mdt_cantidad
FROM m_Inventario I 
INNER JOIN 
v_suma_ticket D ON I.SKU=d.SKU 
INNER JOIN m_Ticket T ON T.MT_Id=D.MT_Id
WHERE @CAU_Id=T.CAU_id AND @MT_Id=T.MT_Id and @CAU_Id=I.CAU_ID

Working perfectly!!!

Thank you very much all for the help!!!

Regards

 

[donutman]

imosri,
That is a good lesson to learn. I had to create sample data to prove that the SUM approach is necessary...and it is. I guess the rule is that an UPDATE will only be performed once per row even if the join creates multiple rows in the update table. That is counter intuitive.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

imosri,
I think you deserve the star more than I do for this thread. I've learned another point from you. The following two blocks of code appear to produce the same result.

   UPDATE A
      SET FldA=B.FldA
      FROM TableA A INNER JOIN TableB B
         ON A.ID=B.ID

   UPDATE TableA
      SET FldA=TableB.FldA
      FROM TableB 
      WHERE TableA.ID=TableB.ID

I wonder if one is faster than the other or are they one in the same?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[imosri]

Donutman, thank you for your comments.

Regarding the need of SUM(), now it is obvious that a 1 to 1 is a must.

Wondering why the other exercises did not performed as desired I found this line of thinking:

Lets say we are inside the update statement where at any given target row has a set of 2 matching values to-be-updated:
finally lets say original value is 10 and values to be updated are 5 and 6...

expected behaviour

iteration 1:
set a=a+ select....
transforms into
set a=10 + 5

iteration 2:
set a=a+ select....
transforms into
set a=15 + 6

Expected result a=21, rigth? NO!!!!

Observed behavior
iteration 1:
set a=a+ select....
transforms into
set a=10 + 5

iteration 2:
set a=a+ select....
transforms into
set a=10 + 6

Observed result 16!!!!

Why?

I think that the answer is: Becasue the update statment is NOT commited to the database until it is completed fully, so no matter what you do value, column "a" from the database always will be 10!

I am tryng to confirm that, but it seems pretty obvious.

It has been a great expencince working this out with you donutman.

Regards

 

[donutman]

imosri,
I formulated a slightly different reason, but have found no way to prove it. I reasoned that the inner join creates a recordset that has no primary key (remember TableA now has contributed duplicate rows). It's those duplicates that need to get updated twice, however, after the first update the field data is changed. Now when the 2nd update is ready to be performed, the record can no longer be located because one of the columns has changed! So the 2nd update fails. This behavior is consistent with doing the update in EM. To test this right click on a table and tell it you want to write a query. Run the intended update as a SELECT query, then update the intended records manually. The 2nd update will fail.
Your explanation isn't consistent with a test that I performed, but the test also suggests that my explanation isn't correct. I performed this update.

   DECLARE @Q as int
   SET @Q=0
   UPDATE A
      SET @Q=@Q+1, ColumnQ=@Q, ColumnX=B.ColumnX
      FROM TableA A INNER JOIN TableB B
      ON A.ID=B.ID

I used sample data that generated 2 rows from 1 row of TableA. The result showed that ColumnQ had a value consistent with the 1st update succeeding, but ColumnX had a value consistent with the 2nd update succeeding. Of course, it's possible that the update order was the reverse of the equivalent SELECT, so there's no way that I know of to be sure how it actually works.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]