我想在两个数据表(cim_customer, cim_erp_adress)中找出a表(cim_erp_address表)中不存在的内容, 再用insert语句把不存在的信息加进表里。所以写了如下语句,执行时出现:“单行子查询返回多于一个行”
insert into cim_erp_address (customer_id,address_type,id_fragment,modify_date) values ((select t.customer_id from cim_customer t
where t.customer_id not in (select a.customer_id from cim_erp_address a)),2,'N','2007-10-15 10:31:24');
commit;
各位大神帮看一下,能不能有什么办法批量加入这些内容。最好是能以原来的
insert into cim_erp_address (customer_id,address_type,id_fragment,modify_date) values ((select t.customer_id from cim_customer t
where t.customer_id not in (select a.customer_id from cim_erp_address a)),2,'N','2007-10-15 10:31:24');
commit;
各位大神帮看一下,能不能有什么办法批量加入这些内容。最好是能以原来的
添加一个或多个记录至一个表。这叫作追加查询.
语法
多重记录追加查询:
INSERT INTO target [(field1[, field2[, ...]])][IN外部数据库]
SELECT field1[, field2[, ...]]
FROM tableexpression
单一记录追加查询:
INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])
-- values (( -- 这个 values 不要select t.customer_id from cim_customer t
where t.customer_id not in (select a.customer_id from cim_erp_address a)),2,'N','2007-10-15 10:31:24');
select t.customer_id,2,'N','2007-10-15 10:31:24' from cim_customer t
where not exists(select 1 from cim_erp_address a where t.customer_id=a.customer_id);
insert into cim_erp_address (customer_id,address_type,id_fragment,modify_date)
select t.customer_id,2,'N','2007-10-15 10:31:24' from cim_customer t
where not exists(select 1 from cim_erp_address a where t.customer_id=a.customer_id);
insert into cim_erp_address (customer_id,address_type,id_fragment,modify_date)
select t.customer_id,2,'N','2007-10-15 10:31:24' from cim_customer t
where not exists(select 1 from cim_erp_address a where t.customer_id=a.customer_id);