mysql交換兩行或兩列的值

Mars-xq發表於2018-12-28
MySql

參考：

mysql left join中where和on條件的區別

一、交換兩列的值（列名不變）

新建資料庫和表，並插入資料

CREATE DATABASE test;

USE test;

CREATE TABLE `product` (
  `id`             INT(10) UNSIGNED       NOT NULL AUTO_INCREMENT
  COMMENT '產品id',
  `name`           VARCHAR(50)            NOT NULL
  COMMENT '產品名稱',
  `original_price` DECIMAL(5, 2) UNSIGNED NOT NULL
  COMMENT '原價',
  `price`          DECIMAL(5, 2) UNSIGNED NOT NULL
  COMMENT '現價',
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `product` (`id`, `name`, `original_price`, `price`) VALUES
  (NULL, '雪糕', '5', '3.5'),
  (NULL, '鮮花', '18', '15'),
  (NULL, '甜點', '25', '12.5'),
  (NULL, '玩具', '55', '45'),
  (NULL, '錢包', '285', '195');

查詢：

SELECT *
FROM product;

在這裡插入圖片描述

按別名查詢：

#
SELECT *
FROM product AS a, product AS b
WHERE a.id = b.id;

在這裡插入圖片描述

交換兩列的值（列名不變）：

# 交換mysql的兩列的值（列名不變）
UPDATE product AS a, product AS b
SET a.original_price = b.price, a.price = b.original_price
WHERE a.id = b.id;

再次查詢：

SELECT *
FROM product;

在這裡插入圖片描述

可看到已改變。

二、交換兩行的值（id不變）

新建資料庫和表，並插入資料

CREATE DATABASE test;

USE test;

CREATE TABLE `rules` (
  `rule_id`   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rule_name` VARCHAR(255)     NOT NULL,
  `priority`  INT(11)                   DEFAULT NULL,
  PRIMARY KEY (`rule_id`)
);

INSERT INTO rules
(rule_id, rule_name, priority)
VALUES
  (1, 'Take one bread', 10),
  (2, 'Drink water', 20),
  (3, 'Eat candy', 30),
  (4, 'Wash hand', 40),
  (5, 'Give charity', 50);

查詢：

SELECT * FROM rules;

在這裡插入圖片描述

按照別名和id查詢：

SELECT *
FROM
  rules AS rule1
  JOIN rules AS rule2 ON
                        (rule1.rule_id = 1 AND rule2.rule_id = 4)
                        OR (rule1.rule_id = 4 AND rule2.rule_id = 1);

在這裡插入圖片描述

交換兩行的值（除了id）

UPDATE
    rules AS rule1
    JOIN rules AS rule2 ON
                          (rule1.rule_id = 1 AND rule2.rule_id = 4)
                          OR (rule1.rule_id = 4 AND rule2.rule_id = 1)
SET
  rule1.priority  = rule2.priority,
  rule2.priority  = rule1.priority,
  rule1.rule_name = rule2.rule_name,
  rule2.rule_name = rule1.rule_name;

再次查詢：

SELECT * FROM rules;

在這裡插入圖片描述

可看到兩行已完成了交換。

相關文章