Home > Database > Mysql Tutorial > body text

MySql insert插入操作的3个小技巧分享_MySQL

WBOY
Release: 2016-06-01 13:04:38
Original
1262 people have browsed it

1.插入的数据来源自其他表

表A有id, cola 字段
表B有id, cola, colb...等字段,其中id都为主键,cola为相同名字的列
现想将表B中colb>1000的记录都插入表A中,SQL语句可以这样写:

代码如下:


insert into A(id,cola) select id,cola from B where colb>1000 

2.插入时排除(忽略)重复记录

现表A中有一部分记录了,再做如1中的插入操作可能遇到重复的key导致操作失败

代码如下:


insert ignore into A(id,cola) select id,cola from B where colb>1000 

使用insert ignore into插入时,会忽略掉表中已经存在的记录

3.插入时遇到重复记录做更新操作

还有一个表C,与表B的结构类似,现需将表C中的数据插入A中,当遇到重复的记录时,更新cola这一列为表C中的值

代码如下:


insert into A(id,cola) select id,cola from C on duplicate key update A.cola=C.cola


Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!