How to structure a sql query for multiple row updates

Question

I am trying to update 10 rows of my mysql database with new data in one query. I have researched this here MYSQL - UPDATE multiple rows with different values in one query and here SQL string value spanning multiple lines in query and here SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported. I have a column 'pair' and 'price' in a table called 'mt5'. When variable $res[number] holding the new value of price, then price increases the database registers only if it is greater than it's existing value. This type layout works:

$q = "UPDATE MT5 SET price = CASE WHEN '$res[10]' > price THEN '$res[10]' ELSE price END WHERE pair in ('EUR/USD')";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));// Register the values & redirect: 
$q = "UPDATE MT5 SET price = CASE WHEN '$res[11]' > price THEN '$res[11]' ELSE price END WHERE pair in ('USD/JPY')";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));// Register the values & redirect:

but I need to have it all in one query. Like this: (but this does not work, see below)

$q = "UPDATE MT5 SET price = (CASE WHEN '$res[10]' > price THEN '$res[10]' 
                                   WHEN '$res[11]' > price THEN '$res[11]' 
                                   WHEN '$res[12]' > price THEN '$res[12]'
                                   WHEN '$res[13]' > price THEN '$res[13]'
                                   WHEN '$res[14]' > price THEN '$res[14]'
                                   WHEN '$res[15]' > price THEN '$res[15]'
                                   WHEN '$res[16]' > price THEN '$res[16]'
                                   WHEN '$res[17]' > price THEN '$res[17]' 
                                   WHEN '$res[18]' > price THEN '$res[18]'  
                                   WHEN '$res[19]' > price THEN '$res[19]'
                               END)
                               WHERE pair in ('EUR/USD', 'USD/JPY', 'GBP/USD', 'EUR/GBP', 'USD/CHF', 'EUR/JPY', 'EUR/CHF', 'USD/CAD', 'AUD/USD', 'GBP/JPY')";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));// Register the values & redirect:

Now it just puts in first value into all the cells of column price (all cell have the value of $res[10]. What am I doing wrong. I also tried it with the 'ELSE price' added to the end, like this:

WHEN '$res[12]' > price THEN '$res[12]' ELSE price

next lines same.... I have tried various other ways and they mostly don't enter anything and give error.

Vamsi Prabhala · Answer 1 · 2016-06-25T15:27:32.097

1

Try to include the pair condition in the case expression.

"UPDATE MT5 SET price = 
(CASE WHEN '$res[10]' > price and pair = 'EUR/USD' THEN '$res[10]' 
      WHEN '$res[11]' > price and pair = 'USD/JPY' THEN '$res[11]' 
      WHEN '$res[12]' > price and pair = 'GBP/USD' THEN '$res[12]'
      WHEN '$res[13]' > price and pair = 'EUR/GBP' THEN '$res[13]'
      WHEN '$res[14]' > price and pair = 'USD/CHF' THEN '$res[14]'
      WHEN '$res[15]' > price and pair = 'EUR/JPY' THEN '$res[15]'
      WHEN '$res[16]' > price and pair = 'EUR/CHF' THEN '$res[16]'
      WHEN '$res[17]' > price and pair = 'USD/CAD' THEN '$res[17]' 
      WHEN '$res[18]' > price and pair = 'AUD/USD' THEN '$res[18]'  
      WHEN '$res[19]' > price and pair = 'GBP/JPY' THEN '$res[19]'
END)
WHERE pair in ('EUR/USD', 'USD/JPY', 'GBP/USD', 'EUR/GBP', 
               'USD/CHF', 'EUR/JPY', 'EUR/CHF', 'USD/CAD', 'AUD/USD', 'GBP/JPY')";

edited Jun 25 '16 at 15:27

answered Jun 25 '16 at 15:06

Vamsi Prabhala

48,685
4
36
58

YES that did it, sorry for the delay (just 2 small typos on the currency column in your answer slowed me up) Otherwise perfect, thank you. – Charles Haughey Jun 25 '16 at 15:22
@CharlesHaughey just to expand slightly the reason why you need to do this is the case statement will return the first true condition. So if $res[10] is greater than every other price you will always match that statement first. So if you wan to do greater than x less than y start with the most limiting condition and expand to more encompassing. – Matt Jun 25 '16 at 16:23
@Matt got that, will keep in mind and study up it's applications. – Charles Haughey Jun 25 '16 at 16:36

How to structure a sql query for multiple row updates

1 Answers1