SQL（五）查询 插入/更新/删除

常用于刷数据

一、mysql：

1、查询插入：把一个数据表查询的结果插入另一个表：

insert into t_user_project1 (user_id, project_id,is_delete, create_time,update_time, is_current_project)SELECT
distinct(task.assigned_charge_id),1,0,NOW(),NOW(),0
from  t_crowd_task_nearest task  where 
task.is_delete = 0 and assign_type = 0 and task.project_id=1;


insert into t_user_project1 (user_id, project_id,is_delete, create_time,update_time, is_current_project)SELECT
distinct(task.assigned_agent_id),1,0,NOW(),NOW(),0
from  t_crowd_task_nearest task  where 
task.is_delete = 0 and assign_type = 1 and task.project_id=1
and task.assigned_agent_id not in (select user_id from t_user_project1 )


INSERT INTO t_menu (
	RESOURCE_CODE,
	RESOURCE_NAME,
	RESOURCE_VALUE,
	PARENT_RESOURCE_ID,
	RESOURCE_TYPE,
	VUE_URL
) SELECT
	'usermanager',
	'用户管理',
	'url',
	id,
	3,
	'/user/usermanager'
FROM
	t_menu
WHERE
	RESOURCE_CODE = 'user'

2、查询更新：mysql使用一个表的数据更新另一张表：

UPDATE tb1,tb2 SET tb1.address=tb2.address WHERE tb1.name=tb2.name

   或连接查询：

UPDATE tb1 a
left join tb2 b  on a.name = b.name
left join tb3 c on b.id = c.user_id
SET a.address=b.address
where a.is_delete = 0 and b.is_delete = 0;

3、查询删除：mysql查询删除不支持from后面的查询语句包含主表名，如菜单表

delete  from t_menu where parent_menu_id in (
 
 select id from t_menu  where menu_id ='user'
 
);

会报错[Err] 1093 - You can't specify target table 't_menu' for update in FROM clause ，可以加个临时表：

delete  from t_menu where parent_menu_id in (
  select e. id  from (
	select id from t_menu where menu_id ='user'
	) e
);

同样的方法来删除重复数据（学校id+学校名称重复的组合）：

DELETE
FROM
	t_school
WHERE
	id IN (
		SELECT
			e.id
		FROM
			(
				SELECT
					min(id) id
				FROM
					t_school
				GROUP BY
					`school_name`,
					`school_id`
				HAVING
					count(1) > 1
			) e
	);

二、sqlServer

1、查询插入：

insert into table_a(account) select user_id from table_b;

2、查询更新： 

Update dbo.table_1 
 set dbo.table_1 .project_code=dbo.table_2.project_code
FROM dbo.table_1 , dbo.table_2  where 
dbo.table_1.project_name=dbo.table_2.project_name;