当SQL UPDATE遇到EXISTS(SELECT ...)时

直接上例子。

user表：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for user

-- ----------------------------

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  `class_id` int(11) DEFAULT NULL,

  `class_name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of user

-- ----------------------------

INSERT INTO `user` VALUES ('1', 'a', '1', '');

INSERT INTO `user` VALUES ('2', 'b', '2', '');

INSERT INTO `user` VALUES ('3', 'c', '1', '');

INSERT INTO `user` VALUES ('4', 'd', '1', '');

INSERT INTO `user` VALUES ('5', 'e', '2', '');

INSERT INTO `user` VALUES ('6', 'f', '2', '');

INSERT INTO `user` VALUES ('7', 'g', '3', '');

INSERT INTO `user` VALUES ('8', 'h', '2', '');

INSERT INTO `user` VALUES ('9', 'k', '2', '');

INSERT INTO `user` VALUES ('10', 'm', '3', '');

class表：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for class

-- ----------------------------

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of class

-- ----------------------------

INSERT INTO `class` VALUES ('1', '初级班');

INSERT INTO `class` VALUES ('2', '中级班');

INSERT INTO `class` VALUES ('3', '高级班');

要求：根据`user`.class_id查询class.name，并用其更新`user`.class_name。

注意：这里测试的是UPDATE 语句与 WHERE EXISTS 语句的执行顺序，所以请忽略表设计方面的问题吧。

语句如下：

UPDATE `user` u

SET u.class_name = (

    SELECT

        class.`name`

    FROM

--          `user` u,

        class

    WHERE

        u.class_id = class.id LIMIT 1

)

WHERE

    EXISTS (

        SELECT

            1

        FROM

--             `user`,

            class

        WHERE

            u.class_id = class.id

    )

结果表明：

UPDATE语句的WHERE语句如果是EXISTS，那每执行一条就会判断一下，成立则执行SET语句 -- 类似于遍历执行。
另外，UPDATE的表不能出现在FROM语句中。

这里的问题在于，EXISTS是bool判断，而SELECT则返回集合，容易让人一头雾水。

其实用PLSQL来写，逻辑更清晰一些。这里放上一个Oracle的PLSQL吧 -- MySQL的语法略有不同，暂没查到~~

-- 打开控制台输出

set serveroutput on

-- PLSQL

DECLARE

    -- 设置光标

    CURSOR c is SELECT id,`name` FROM class;

    -- 定义光标变量

    pid        class.id%type;

        pname class.name%type;

BEGIN

    -- 打开光标

    OPEN c;

    LOOP    -- 循环取出光标中的数据

        FETCH c INTO pid,pname; -- 取出的数据放入变量中

        EXIT WHEN c%notfound;  -- 退出条件

        -- 更新数据

        UPDATE `user` SET `user`.class_name = pname WHERE `user`.class_id = pid;

    END LOOP;

    CLOSE c;

    -- 对于ORACLE，默认的事务隔离级别是 read committed。所以需要commit

    COMMIT;

--     dbms_output.put_line('涨薪完毕');   -- 控制台输出完成提示

END;

/   -- 执行

巴特西

当SQL UPDATE遇到EXISTS(SELECT ...)时

最新文章

热门文章