• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Swap function in MySQL?

Hi,

Given the ID number of 134, i want to swap it with ID 145.

For example from:
ID      Sequence   Name
================
132   1                  Andy
134   2                  Ethan   -> want to swap this row with...
145   3                  Matt     -> .... this row

to:

ID      Sequence   Name
================
132   1                  Andy
134   2                  Matt     '-> This has been swap with Ethan
145   3                  Ethan

How do i execute the above by using MySQL query??

Please advise.

0
tangteng78
Asked:
tangteng78
1 Solution
 
cyberstalkerCommented:
Something like this perhaps? You can make it into a function too:
UPDATE table SET Name = CASE ID WHEN 134 THEN (SELECT Name FROM table WHERE ID = 145) WHEN 145 THEN (SELECT Name FROM table WHERE ID = 134) END WHERE ID IN (134, 145);
 
DELIMITER //
CREATE PROCEDURE swap_names(id1 INT, id2 INT)
BEGIN
	UPDATE table SET Name = CASE ID WHEN id1 THEN (SELECT Name FROM table WHERE ID = id2) WHEN id2 THEN (SELECT Name FROM table WHERE ID = id1) END WHERE ID IN (id1, id2);
END//
DELIMITER ;

Open in new window

0
 
tangteng78Author Commented:
Is there a way to refer to a row before/after a specifc row id?

Say i have the ID 134, is there a way to get the previous ID 132 without having to manually specify it in the query? And also the next ID 145.
0
 
tcs224694Commented:
U can use curval and nextval for that...
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
tangteng78Author Commented:
any example is greatly appreciated?
0
 
NerdsOfTechTechnology ScientistCommented:
I can help you if you still need the answer.
0
 
NerdsOfTechTechnology ScientistCommented:
First we will find if in fact there is a record below ID=134

Next we will swap. Are you going to swap up and down.

Let me know first.
SELECT *, (SELECT sequence FROM `Sequence` a WHERE ID = 134) as seq FROM `Sequence` b HAVING seq <= Sequence LIMIT 2

Open in new window

SwapFind.png
0
 
NerdsOfTechTechnology ScientistCommented:
In other words, would you like to also be able to reference an ID to "go up" a sequence?

Thanks. Let me know before we goto step 2
0
 
NerdsOfTechTechnology ScientistCommented:
One last question did you want to swap records regardless of sequence positioning.

EG.

lets say you had a list:
1|1|a
2|2|b
3|3|c
6|4|d
12|5|e
18|6|f

would you like to be able to swap a's sequence with e's sequence?
or only move "up" or "down"

Thanks again!

=NerdsOfTech
0
 
NerdsOfTechTechnology ScientistCommented:
If I can answer any question related to this question PLEASE post this as a RELATED question. That way I can help answer it.

Best regards,
NerdsOfTech
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now