2

For simplicity say I have a column 'Hour' which is either the value 10, or 12. I wish to update a new column, Hour_type which will be 'A' if the Hour value is 10 or B if 12 and so on. I can output a column, 'Hour_type' by using CASE, as follows

SELECT CASE WHEN Hour = 10 then 'A'
            WHEN hour = 12 then 'B'
            else 'c'
        end
as Hour_type
from Traffic_Counts

This outputs the correct answer but does not insert the values into the table.

I wish to set a column which exists in the table with these values. 

SELECT CASE WHEN Hour = 10 then 'A'
            WHEN hour = 12 then 'B'
        end
as Hour_type
from Traffic_Counts set Hour_type = Hour_type

This results in a Syntax error.

In pseudocode I am trying to add an 'if' to this simple update column 

update table set Hour_type = 'a' if Hour = 10,
                             'b' if Hour = 12;
LearningSlowly
  • 8,641
  • 19
  • 55
  • 78

3 Answers3

1

Use this, but i am not sure what are you doing. This return 'a', 'b' or 'X' if the HOUR not 10 or 12

update table set Hour_type = IF(HOUR = 10 , 'a', IF(HOUR = 12, 'b', 'X'));
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
1

The case [...] end block is an expression. update is used to set columns from expressions. So, just take the entire case [...] end block and make that the right-hand-side of an update to your desired column. This would work for any other selectable expression, too (barring conflicting types, etc.)

underscore_d
  • 6,309
  • 3
  • 38
  • 64
  • Thanks. I tried, is this what you mean? update Hour_type SELECT CASE WHEN Hour = 10 then Hour_type = 'A' WHEN hour = 12 then Hour_type = 'B' else Hour_type = 'c' end – LearningSlowly Oct 13 '15 at 12:13
  • Close... but please learn the syntax of `update`. It would be `update yourTable set yourColumn = yourExpression[, yourOtherColumn = blahBlah];` where the square brackets are optional and can be repeated for multiple columns. So, in this case, `update yourTable set yourColumn = case [...] end`. – underscore_d Oct 13 '15 at 12:15
  • Many thansk for the help. So, I tried "update Traffic_Counts set Hour_type = case [when Hour = 10 then 'A', when Hour = 12 then 'B', else 'C'] end" but no avail. Am I correct in saying there should be , between the different when statements? – LearningSlowly Oct 13 '15 at 13:39
  • No, there should not be commas there. As I said, just put in the `case` block the same as it was in the original `select`. And the square brackets were just shorthand to indicate I was missing out code; do not include those. Plenty more info could be found in the documentation for these keywords. – underscore_d Oct 13 '15 at 13:47
0

you can use case statement to update records.Go through the link for query

MySQL CASE...WHERE...THEN statements

Community
  • 1
  • 1
Atul Raj
  • 21
  • 2