CODEWITHSUNDEEP
mysqlsql
Jonathan Bishop
Jonathan Bishop

Reputation: 167

How to update MySQL table by swapping two column values?

I have three tables such that

CREATE TABLE guest(
name varchar(100),
ranking int,
PRIMARY KEY (name)
);

CREATE TABLE room(
roomname varchar(100),
wallcolor varchar(100),
rating int,
PRIMARY KEY(roomnane)
);

CREATE TABLE reservation(
name varchar(100),
roomname varchar(100),
day varchar(100),
moveinday int,
moveoutday int,
PRIMARY KEY(roomname, day, start, finish),
FOREIGN KEY(roomname) REFERENCES room(roomname),
FOREIGN KEY(name) REFERENCES guest(name)
);

I'm trying to change all the "Lake" rooms to "Bay" rooms and "Bay" rooms to "Lake" rooms without explicitly changing the rooms involved. How could I do this? I tried using the update query but I am not sure how to accomplish this.

Upvotes: 2

Views: 69

Answers (1)

1000111
1000111

Reputation: 13519

UPDATE room
SET roomname = (
    CASE
    WHEN roomname = "Bay" THEN
        "Lake"
    WHEN roomname = "Lake" THEN
        "Bay"
    ELSE
        roomname
    END
)

EXample:

If your room table looks like below before running this query:

enter image description here

Then after running the above query your room table would look like leaving other rooms unchanged:

enter image description here

Upvotes: 4

Related Questions