Switch values in one column at same time in MySQL

Discussion in 'Databases' started by stephwalt, Oct 17, 2009.

  1. #1
    Please help, I am new to database.
    I need to write a MySQL statement that swaps two values in one colmn at same time.

    The column is called Experiencelevel. The values are 'junior' and 'senior'.
    I need to make all values that are currently junior to senior and all values that are currently 'senior' to 'junior'.

    I need to use the update statment so I know to change one I would use

    Update employee
    set experiencelevel = 'junior' where experience level = 'senior ;

    the problem is that I can not figure out how to do both at one time so they will actually switch.

    If anyone can help, I would greatly appreciate it.
    Thank you
     
    stephwalt, Oct 17, 2009 IP
  2. rayqsl

    rayqsl Active Member

    Messages:
    91
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    53
    #2
    I don't know if this works but it looks about right - make sure you test it properly on a test/development database

    update employee
    set experiencelevel = case when experiencelevel = 'junior' then 'senior' else 'junior' end
     
    rayqsl, Oct 18, 2009 IP
  3. Chicken Run

    Chicken Run Peon

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    You can do this

    First, You read from column1 to temporary variable. col1 -> TMP
    Second, You update two column. For example. col2 -> col1
    Third, You update from temporary variable to column 2. tmp -> col2

    Or

    You can use temporary data on your query. You use random string for temporary data and it must be not in that column before

    UPDATE employee SET experiencelevel = 'qwertyuiop' WHERE experiencelevel = 'senior' ;
    UPDATE employee SET experiencelevel = 'junior' WHERE experiencelevel = 'senior' ;
    UPDATE employee SET experiencelevel = 'junior' WHERE experiencelevel = 'qwertyuiop' ;
     
    Chicken Run, Oct 18, 2009 IP
  4. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    And you need to perform these three updates within a begin/commit block to have consistency in your database in case the server crashes after the first or second statement.

    ACID compliant DB system are required for this, think Postgresql or Mysql WITH innodb tables.
     
    chisara, Oct 19, 2009 IP