查询插入（更新/删除）

矫情吗；* 2022-02-03 01:27 275阅读 0赞

查询插入（更新）可用于刷数据，新建一个中间表，导入数据，再把数据刷到目标表上

一、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
    );

二、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;