Geek Answers Handbook

Mysql replace two values ​​in one query?

front

id | cat_id | order 33 | 1 | 1 34 | 1 | 2

after

 id | cat_id | order 33 | 1 | 2 34 | 1 | 1

Now using 4 queries

$ db is a $ mysqli wrapper for using a protective shell and injection protection

get the first record by id

 $curr = $db->q('SELECT id,order,cat_id FROM `tbl` WHERE id`=? FOR UPDATE', 33)->fetch_assoc();

if the first record exists, find the next record in the order field

 if($curr){ $next = $db->q('SELECT id,order FROM `tbl` WHERE `cat_id`=? AND `order`>? ORDER BY `order` LIMIT 1 FOR UPDATE', $curr['cat_id'],$curr['order']));

if the first and second value of the order of changing the order of replacement

  if($prev['id']){ $db->q("UPDATE `tbl` SET `order`=? WHERE `id`=?",$next['order'],$curr['id']); $db->q("UPDATE `tbl` SET `order`=? WHERE `id`=?",$curr['order'],$next['id']); } }

Attention! Check for two records, lock rows for updates

+4
source share
3 answers

MySQL does not support updating with the same table in the FROM statement . Therefore, because of this, there are (select * from TBL) as t2 in the internal subqueries.

Also, the EXISTS condition in the first CASE WHEN is to prevent updating if the second record does not exist ("if the first and second records exist, change the order value")

Here is an SQLfiddle example

 UPDATE tbl as t1 SET `order`= CASE WHEN id = 33 and EXISTS (SELECT ID from (select * from TBL) t2 where cat_id=t1.Cat_Id and `order`>t1.`order` ORDER BY `order` LIMIT 1) THEN (SELECT `order` from (select * from TBL) t2 where cat_id=t1.Cat_Id and `order`>t1.`order` ORDER BY `order` LIMIT 1) WHEN id <>33 THEN (SELECT `order` from (select * from TBL) t2 where cat_id=t1.Cat_Id and `order`<t1.`order` ORDER BY `order` DESC LIMIT 1 ) ELSE `order` END where id =33 or (SELECT ID from (select * from TBL) t2 where cat_id=t1.Cat_Id and `order`<t1.`order` ORDER BY `order` DESC LIMIT 1) =33
+2
source

With one request, this is:

 UPDATE `tbl` SET `order`=CASE WHEN `order`=2 THEN 1 WHEN `order`=1 THEN 2 END; WHERE `order` IN (1,2)

or, for id condition:

 UPDATE `tbl` SET `order`=CASE WHEN `order`=2 THEN 1 WHEN `order`=1 THEN 2 END; WHERE id = $id
+1
source

To change 2 fields by id line try:

 UPDATE `tbl` AS tbl1 JOIN `tbl` AS tbl2 ON ( tbl1.id = 33 AND tbl2.id = 34 ) SET tbl1.order = tbl2.order, tbl2.order = tbl1.order

You can also set the desired value instead of swap between two files. If necessary, you can add a where clause, as shown below, to replace where cat_id 1 in two lines:

 WHERE tbl1.cat_id = 1 AND tbl2.cat_id = 1

Update: If your order numbers are unique to any cat_id , you can try as follows:

 UPDATE `tbl` AS tbl1 JOIN `tbl` AS tbl2 ON ( tbl1.order = 1 AND tbl2.order = 2 ) SET tbl1.order = tbl2.order, tbl2.order = tbl1.order WHERE tbl1.cat_id = 1 AND tbl2.cat_id = 1

It works if your order field is int . Otherwise, you must specify order values ​​in the request.

See the result on SQLFiddle

+1
source

More articles:


All Articles