联表多字段update更新语句
  前⾔
  最近需要写联表多字段update更新语句，发现不同的数据库，SQL语法也不⼀样，正好我这⾥有MySQL、Oracle、PgSQL三种数据库环境，分别练习、实操这三种数据库的联表update语句
  本⽂记录MySQL、Oracle、PgSQL联表多字段update更新语句实操练习过程
  练习需求
  ⼀张user⽤户表、⼀张blog博客表，由于不同的数据库，sql语法不⽤，这⾥给出mysql的表数据语句，其他数据库类型⾃⾏转换
-- user⽤户表
答案一零九六下载CREATE TABLE `user`  (
`user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '⽤户id',
`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '⽤户名称',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET= utf8 COLLATE = utf8_general_ci COMMENT ='⽤户表' ROW_FORMAT = Compact;
INSERT INTO `user` VALUES ('1', '张三');
INSERT INTO `user` VALUES ('2', '李四');
INSERT INTO `user` VALUES ('3', '王五');
-- blog博客表
CREATE TABLE `blog`  (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '博客id',
`title` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMM
ENT '博客标题',
`content` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '博客内容',
`user_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '⽤户id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT =17CHARACTER SET= utf8 COLLATE = utf8_general_ci COMMENT ='博客表' ROW_FORMAT = Compact;
INSERT INTO `blog` VALUES (2, 'test 1', 'test 1', '1');
INSERT INTO `blog` VALUES (3, 'test 2', 'test 2', '1');
INSERT INTO `blog` VALUES (4, 'test 4', 'test 4', '2');
INSERT INTO `blog` VALUES (5, 'test 5', 'test 5', '2');
INSERT INTO `blog` VALUES (6, 'test 6', 'test 6', '1');
INSERT INTO `blog` VALUES (11, '11', '11', '3');
INSERT INTO `blog` VALUES (12, '12', '12', '3');
INSERT INTO `blog` VALUES (13, '13', '13', '3');
INSERT INTO `blog` VALUES (14, '14', '14', '3');
多态继承封装INSERT INTO `blog` VALUES (15, '15', '15', '3');
INSERT INTO `blog` VALUES (16, '16', '16', '3');
  练习需求：更新每个⽤户的博客id最⼤的博客内容，新内容为：⽤户名称user_name + 博客内容content
  需要修改的id，以及新内容
  SQL
  mysql
select*from blog
select*from `user`
-- 查出虚表table1
-- 练习需求：更新每个⽤户的博客id最⼤的博客内容，新内容为：⽤户名称user_name + 博客内容content
SELECT
t.id,
concat( t.user_name, b.content ) content
FROM
blog b
JOIN (
SELECT
u.user_id,
u.user_name,
max( b.id ) AS id
FROM
blog b
JOIN `user` u ON u.user_id= b.user_id
GROUP BY
u.user_id,
u.user_name
) t ON b.id = t.id
-- MySQL联表多字段update更新语句
scalar i500
update blog b ,(table1) t t = t.content where b.id = t.id
  为了⽅便阅读避免贴出⼀堆长长的sql，此时update语句并不完整，执⾏时需要将table1虚表的查询sql，替换到下⾯的update语句中table1，再执⾏update语句
  oracle
select*from "blog"
select*from "user"
-- 查出虚表table1
-- 练习需求：更新每个⽤户的博客id最⼤的博客内容，新内容为：⽤户名称user_name + 博客内容content
SELECT
t."id",
concat( t."user_name", b."content" ) AS "content"
FROM
"blog" b
JOIN (
SELECT
u."user_id",
u."user_name",
max( b."id" ) AS "id"
FROM
"blog" b
JOIN "user" u ON u."user_id" = b."user_id"
GROUP BY
u."user_id",
u."user_name"
) t ON b."id" = t."id"
-- Oracle联表多字段update更新语句
update "blog" b set (b."content") = (select t."content" from (table1) t where b."id" = t."id")
where exists (select1from (table1) t where b."id" = t."id")
  为了⽅便阅读避免贴出⼀堆长长的sql，此时update语句并不完整，执⾏时需要将table1虚表的查询sql，替换到下⾯的update语句中table1，再执⾏update语句
  pgsql
select*from "blog"
select*from "user"
-
- 练习需求：更新每个⽤户的博客id最⼤的博客内容，新内容为：⽤户名称user_name + 博客内容content
SELECT
t."id",
concat( t."user_name", b."content" ) AS "content"
FROM
"blog" b
JOIN (
SELECT
u."user_id",
u."user_name",
凯撒密码翻译器在线max( b."id" ) AS "id"
FROM
"blog" b
JOIN "user" u ON u."user_id" = b."user_id"
GROUP BY
u."user_id",
u."user_name"
) t ON b."id" = t."id"
-- PgSQL联表多字段update更新语句
update "blog" b set b."content" = t."content" from (table1) t where b."id" = t."id"
  为了⽅便阅读避免贴出⼀堆长长的sql，此时update语句并不完整，执⾏时需要将table1虚表的查询sql，替换到下⾯的update语句中table1，再执⾏update语句
  练习效果
  以上三种数据库类型，SQL执⾏结果均为
  上⼤招
  实在不⾏了，可以直接拼接出update语句，再把update语句复制出来执⾏！
  我们以mysql为例（PS：由于单引号'，是特殊字符，拼接时我们⽤$代替，后⾯再进⾏全部替换即可）
-- 查看、对⽐新旧数据
SELECT
t.id,
concat( t.user_name, b.content ) new_content
-- 备份原数据
SELECT
concat(
concat( 'update blog set content = $', b.content ),
concat( '$ where id = $', concat( t.id, '$;' ))
) AS str
SELECT
concat(
concat( 'update blog set content = $', concat( t.user_name, b.content ) ),
concat( '$ where id = $', concat( t.id, '$;' ))
) AS str
FROM
blog b
JOIN (
SELECT
u.user_id,
u.user_name,
max( b.id ) AS id
FROM
blog b
JOIN `user` u ON u.user_id= b.user_id
GROUP BYjava for循环语句
u.user_id,
u.user_name
) t ON b.id = t.id
  查看、对⽐新旧数据
  备份原数据
  update语句
  这时候就可以愉快的执⾏update语句了
  后记
  PS：执⾏update语句⼀定要带上where条件，否则⼀不⼩⼼就变成更新全表
mysql语句多表查询
  联表多字段update更新语句暂时先记录到这，后续再进⾏补充

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。