表A和表B结构完全一样。A表数据要更新到B表去。两表通过字段ID来关联。
如果字段IMPORTDIRECTION=1,A.ID等于B.ID,则把A表中对应ID的那条数据update到B表去,然后把A表中的字段IMPORTDIRECTION修改成4.
如果字段IMPORTDIRECTION=1,A.ID不等于B.ID,说明是新数据。则把A表中对应ID的那条数据insert into到B表去,然后把A表中的字段IMPORTDIRECTION修改成4.请问下语句该如何写
如果字段IMPORTDIRECTION=1,A.ID等于B.ID,则把A表中对应ID的那条数据update到B表去,然后把A表中的字段IMPORTDIRECTION修改成4.
如果字段IMPORTDIRECTION=1,A.ID不等于B.ID,说明是新数据。则把A表中对应ID的那条数据insert into到B表去,然后把A表中的字段IMPORTDIRECTION修改成4.请问下语句该如何写
MERGE INTO B
USING
(
SELECT ID,COL_1,COL_2...
FROM A
WHERE A.IMPORTDIRECTION=1
)A
ON (B.ID=A.ID)
WHEN MATCHED THEN UPDATE SET
B.COL_1=A.COL_1,
B.COL_2=A.COL_2,
...
WHEN NOT MATCHED THEN INSERT (ID,COL_1,COL_2...)
VALUE(A.ID,A.COL_2,...);UPDATE A SET IMPORTDIRECTION=4;
MERGE INTO B
USING
(
SELECT ID,COL_1,COL_2...
FROM A
WHERE A.IMPORTDIRECTION=1
)A
ON (B.ID=A.ID)
WHEN MATCHED THEN UPDATE SET
B.COL_1=A.COL_1,
B.COL_2=A.COL_2,
...
WHEN NOT MATCHED THEN INSERT (ID,COL_1,COL_2...)
VALUE(A.ID,A.COL_2,...);
这个语句跟update select insert都是一样的,你可以用任何一种执行update、insert语句的方法来执行这条语句。
USING
(
SELECT ID,
name,
phone,
mobile,
address,
buyMachineType,
buyDate,
buyAddress,
buyPrice,
customerType,
vipCard,
content,
email,
area_id,
area_code,
area_name,
importDirection
FROM tbl_customer_amt_temp A
WHERE A.IMPORTDIRECTION=1
)A
ON (B.ID=A.ID)
WHEN MATCHED THEN UPDATE SET
B.name=A.name,
B.phone=A.phone,
B.mobile=A.mobile,
B.address=A.address,
B.buyMachineType=A.buyMachineType,
B.buyDate=A.buyDate,
B.buyAddress=A.buyAddress,
B.buyPrice=A.buyPrice,
B.customerType=A.customerType,
B.vipCard=A.vipCard,
B.content=A.content,
B.email=A.email,
B.area_id=A.area_id,
B.area_code=A.area_code,
B.area_name=A.area_name,
B.importDirection=A.importDirection
WHEN NOT MATCHED THEN INSERT (ID,name,
phone,
mobile,
address,
buyMachineType,
buyDate,
buyAddress,
buyPrice,
customerType,
vipCard,
content,
email,
area_id,
area_code,
area_name,
importDirection)
values(A.ID,A.name,A.phone,A.mobile,A.address,A.buyMachineType,
A.buyDate,A.buyAddress,A.buyPrice,A.customerType,A.vipCard,A.content,A.email,A.area_id,A.area_code,
A.area_name,
A.importDirection);
我根据你给的那SQL,写了这段代码,但是执行的时候没反应,按照道理来说,表里才2个数据,不可能要很久时间的。帮忙看下是哪里错了,分等下再给你,我怕结贴了就不能再回复了,先说声谢谢了