Mysql replace two value in one query?

Question

BEFORE

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

AFTER

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

Now using 4 query

$db is wrap $mysqli for using placeholder and injection defense

get first record by id

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

if exist first record find next record by 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 exist first and second recorn change order value

  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']);
  }
}

Important! Checking exist two record, lock rows for update

score 2 · Accepted Answer · edited May 23 '17 at 11:50

MySQL doesn't support update with the same table in the FROM statement. So because of this there are (select * from TBL) as t2 in inner subqueries.

Also EXISTS condition in the first CASE WHEN is to prevent update if the second record doesn't exists ("if exist first and second records change order value")

Here is a 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

Alma Do · Answer 2 · 2013-09-20T08:51:58.267

1

With one query it's:

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

or, for id's condition:

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

edited Sep 20 '13 at 08:51

answered Sep 20 '13 at 08:46

Alma Do

37,009
9
76
105

@ypercube, sorry, was distracted with another SO comment answer and postponed my edit. Thank you. – Alma Do Sep 20 '13 at 08:52

Vahid Hallaji · Answer 3 · 2013-09-20T10:43:27.760

1

To swap 2 fields by row id 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

Also you can set your desired value instead of swap between 2 fileds. If needed, you can add a where clause like below to swap where cat_id are 1 in two rows:

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

Update: If your order numbers are unique for any cat_id you can try this way:

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 should quote order values in query.

See the result on SQLFiddle

edited Sep 20 '13 at 10:43

answered Sep 20 '13 at 08:53

Vahid Hallaji

7,159
5
42
51

@ypercube Sure, I don't mean unique index. It is not fill here. For every cat, there is a duplicate order in this case. – Vahid Hallaji Sep 20 '13 at 09:28
1

I'll give +1 for the interesting idea and because the update will happen only if there are two rows (with `order` 1 and 2) and no update at all if only one exists. – ypercubeᵀᴹ Sep 20 '13 at 10:16

Mysql replace two value in one query?

3 Answers3