Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts

    cycling values on update, null

    Code:
    update table set field = 
    (SELECT CASE field 
    when null then 1 
    when 1 then 2 
    else null end)
    does not work , because null != null inside "case when". That is allso stated in user comments in documentation for Case.

    Any other suggestion how to do this ?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,178
    Thanks
    75
    Thanked 4,339 Times in 4,305 Posts
    A couple of alternatives.

    Code:
    update table set field = 
        ( CASE WHEN field IS NULL THEN 1
               WHEN field = 1 THEN 2
               ELSE null END )
    (No SELECT needed, by the by.)

    Or:
    Code:
    UPDATE table SET field = IF(field IS NULL, 1, IF(field = 1, 2, null) )


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •