update语句的语法，一定要加上exists这种，这样限定更新的范围

最新推荐文章于 2024-04-28 22:44:07 发布

伍世碧

最新推荐文章于 2024-04-28 22:44:07 发布

阅读量1.6k

点赞数 2

分类专栏：笔记文章标签： sql mysql mybatis

本文链接：https://blog.csdn.net/wusb5/article/details/128654527

版权

笔记专栏收录该内容

26 篇文章 1 订阅

订阅专栏

update语句的语法，一定要加上exists这种，这样限定更新的范围

UPDATE TEST_TB01 aa
   SET (aa.part_id, aa.flag) =
       (SELECT bb.part_id, bb.flag
          FROM TEST_TB02 bb
         WHERE aa.sensor_id = bb.sensor_id)
 WHERE EXISTS
 (SELECT 1 FROM TEST_TB02 cc 
       WHERE aa.sensor_id = cc.sensor_id);

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE NOT EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

  DELETE  FROM
TEST_TB01 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);