CODEWITHSUNDEEP
mysqlsqlsql-update
Jamie Hartnoll
Jamie Hartnoll

Reputation: 7341

Efficient Update SQL, updating multiple rows with one SQL statement, Avoiding Loops

I'm trying to avoid database access in loops within a project I am working on. Not being too good with SQL, I'm not sure of the best way to approach this.

I'm updating a stock level database in a sale procedure with multiple stock locations/pick locations.

Therefore, this is what I am doing.

Looping through Product IDs, then looping through the pick locations for each product and updating quantities as it goes, like:

For Each wProductId In calculatedProds.Keys '' loop through products requested passing values of pick locations
        For i = 0 To locationCount ' split the location value from the string as per above
            Dim thisLocation As Integer = locationID
            Dim thisQty As Integer = qtyPicked                
            Dim sql As String = "UPDATE `stockLevels` SET `stockLevel`=`stockLevel` - '" & thisQty & "' WHERE `stockLocation`='" & thisLocation & "' AND `id`='" & wProductId & "'"
            ' DO DATA ACCESS WITH SQL ABOVE       
        Next
Next

Of course this works, but it is opening a new Database Connection for every stock location, for every item.

So how would I work this into a single Update Statement?

http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

That link gets me very close to what I am after, I think, but I am not 100% sure how to dynamically build that SQL statement and how to add two conditions to the CASE.

I need to build a SQL Statement something like:

UPDATE stockLevels
SET stockLevel= CASE id
    WHEN '"& wProductId  &"' AND stockLocation='"& thisLocation &"' THEN `stockLevel` - '" & thisQty & "'
    WHEN '"& NEXTwProductId  &"' AND stockLocation='"& NEXTthisLocation &"' THEN `stockLevel` - '" & NEXTthisQty & "'
END

But that's not correct where I am adding the second parameter to the CASE!

I am using MySQL and VB.NET, as usual, any help much appreciated.

Upvotes: 1

Views: 3730

Answers (2)

Andriy M
Andriy M

Reputation: 77667

Your CASE expression is simply incorrect syntactically.

There are two kinds of CASE expressions, almost identical to each other and yet slightly different in syntax.

One has the form of

CASE expr
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE result_else
END

The other looks like this:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE result_else
END

And you were essentially attempting to mix these two kinds of CASE.

You probably just need to use the second one (also called search CASE, if I am not much mistaken):

...
CASE
  WHEN id = '"& wProductId  &"' AND stockLocation='"& thisLocation &"' THEN ...
  WHEN id = '"& NEXTwProductId  &"' AND stockLocation='"& NEXTthisLocation &"' THEN ...
...

Note that if there's no match and the CASE has no ELSE part, the result will be NULL, so make sure you've covered all the cases, otherwise use an ELSE part like this:

ELSE `stocklevel`

I.e. the CASE will evaluate to the original value of the column being updated, rendering no update for it in the end.

Upvotes: 1

a'r
a'r

Reputation: 36999

Using a case statement in the set clause in your example isn't ideal for a number of reasons.

  • There is no where clause to help the database execute the query efficiently
  • The size of the query for large numbers of updates becomes excessive (consider updating 1000 rows like this)
  • You are manually implementing a join - the database can almost certainly do this more efficiently than you.
  • Debugging such a query is also difficult.

Instead, you should first measure the performance of the update one at a time approach to see if you actually need to make improvements.

If performance improvements are required, then I would suggest an approach where the updates are first bulk inserted into a temporary table. A suitable table would have the following columns:

wProductID, stockLocation, newStockLevel

The updates can be bulk inserted using the following MySQL syntax:

INSERT INTO temp_stock_updates
    (wProductID, stockLocation, newStockLevel)
VALUES
    (?,?,?), (?,?,?), (?,?,?), ...

And then a single update is run to update the main table. This query would look something like this:

UPDATE stockLevels s
    JOIN temp_stock_updates u USING (wProductID, stockLocation)
SET
    s.stockLevel = u.newStockLevel

Upvotes: 6

Related Questions