mysql update join sql语句总结
来源:互联网 发布:51单片机视频教程下载 编辑:程序博客网 时间:2024/05/20 07:20
首先注意的是你用的是mysql还是sqlserver
此处是针对mysql 进行join进行更新的
mysql> select * from goods;
+------+----------+-------+--------+
| id | name | price | cat_id |
+------+----------+-------+--------+
| 9 | meizu | 67 | 33 |
| 1 | pingguo | 30 | 11 |
| 2 | pear | 33 | 22 |
| 3 | tao | 34 | 33 |
| 4 | apple | 35 | 11 |
| 5 | hetao | 30 | 22 |
| 6 | huasheng | 33 | 33 |
| 7 | xiaomi | 55 | 11 |
| 8 | huawei | 65 | 22 |
+------+----------+-------+--------+
mysql> select * from sales;
+------+------+--------+
| id | cnt | cat_id |
+------+------+--------+
| 1 | 1 | 11 |
| 2 | 2 | 22 |
| 3 | 3 | 33 |
+------+------+--------+
update sales a join goods b on a.id=b.id set a.cat_id=b.cat_id ;
假定我们有两张表,一张表为Product表存放产品信息,其中有产品价格列Price;另外一张表是ProductPrice表,我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%。
在MySQL中我们有几种手段可以做到这一点,一种是update table1 t1, table2 ts ...的方式:
<span class="pln" style="font-family: Arial, 宋体;">UPDATE product p</span><span class="pun" style="font-family: Arial, 宋体;">,</span><span class="pln" style="font-family: Arial, 宋体;"> productPrice ppSET pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">*</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">0.8</span><span class="pln" style="font-family: Arial, 宋体;">WHERE p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productIdAND p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateCreated </span><span class="pun" style="font-family: Arial, 宋体;"><</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="str" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">'2004-01-01'</span>
另外一种方法是使用inner join然后更新:
<span class="pln" style="font-family: Arial, 宋体;">UPDATE product pINNER JOIN productPrice ppON p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productIdSET pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">*</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">0.8</span><span class="pln" style="font-family: Arial, 宋体;">WHERE p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateCreated </span><span class="pun" style="font-family: Arial, 宋体;"><</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="str" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">'2004-01-01'</span>
另外我们也可以使用left outer join来做多表update,比方说如果ProductPrice表中没有产品价格记录的话,将Product表的isDeleted字段置为1,如下sql语句:
<span class="pln" style="font-family: Arial, 宋体;">UPDATE product pLEFT JOIN productPrice ppON p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productIdSET p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">deleted </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">1</span><span class="pln" style="font-family: Arial, 宋体;">WHERE pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId IS </span><span class="kwd" style="font-family: Arial, 宋体; color: rgb(0, 0, 139);">null</span>
另外,上面的几个例子都是两张表之间做关联,但是只更新一张表中的记录,其实是可以同时更新两张表的,如下sql:
<span class="pln" style="font-family: Arial, 宋体;">UPDATE product pINNER JOIN productPrice ppON p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productIdSET pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">*</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">0.8</span><span class="pun" style="font-family: Arial, 宋体;">,</span><span class="pln" style="font-family: Arial, 宋体;">p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateUpdate </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> CURDATE</span><span class="pun" style="font-family: Arial, 宋体;">()</span><span class="pln" style="font-family: Arial, 宋体;">WHERE p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateCreated </span><span class="pun" style="font-family: Arial, 宋体;"><</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="str" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">'2004-01-01'</span>
两张表做关联,更新了ProductPrice表的price字段
参考 http://blog.csdn.net/yukaizhao/article/details/8280257
- mysql update join sql语句总结
- mysql 多表 update sql语句总结
- mysql 多表 update sql语句总结
- mysql 多表 update sql语句总结
- mysql 多表 update sql语句总结
- mysql:sql update语句
- Mysql跨表更新 多表update sql语句总结
- Mysql跨表更新 多表update sql语句总结
- SQL语句总结:JOIN、UNION
- SQL---DML---UPDATE---UPDATE语句总结
- Mysql多表update sql语句
- MySql update inner join!MySql跨表更新 多表update sql语句?如何将select出来的部分数据update到另一个表里面?
- MySql update inner join!MySql跨表更新 多表update sql语句?如何将select出来的部分数据update到另一个表里面?
- mysql update 的时候使用left join和where语句
- delete update sql join
- mysql sql语句 update 加法运算
- MySql与Sql Server Update语句
- MySql Sql语句总结
- Cordova 环境搭建及使用(下篇)
- 试问,若在try语句块里有一个return语句,同时这个try语句块后面还有一个finally语句块,请问finally语句块里的代码会不会被执行?若答案是肯定的,那么会在什么时候被执行?是在retu
- 重写jquery的ajax的error方法,可处理后台的权限验证,登陆超时等状态码
- listview加载性能优化ViewHolder
- RecyclerView列表控件漂亮时间线实现
- mysql update join sql语句总结
- 网络框架Volley和OkHttp总结
- javascript promise
- python连接mongodb数据库
- 常用正则表达式———为了少板砖
- Bison笔记
- python 获取系统相关编码的函数
- linux编程常用的命名
- Codeforces 191A Dynasty Puzzles【dp】