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
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
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' ;
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.