匹配2个表 - 然后插入或修改到新表 [英] Match 2 tables - then insert or modify to new table

问题描述

我试图在两个表中找到匹配项。首先我将比较电话号码和姓氏等。我将在新表中插入匹配。

我的问题是第一，如果它会更快

如果SQL更好，我需要一些帮助;

我想检查匹配表中是否存在该记录，如果它只应该修改此记录，请指定有其他匹配。

我可以做一个单独的插入和修改查询每种类型的匹配。我只是想知道是否有一个更好的方式做。

我试过合并，它的工作是插入，但修改它给我一个错误，请看下面。

  MERGE匹配AS M 
 USING（SELECT DOE.REG，sl.id FROM DOE INNER JOIN SL ON DOE.TEL = sl.phone）AS DOE 
 On M.doeid = DOE.reg 
 WHEN MATCHED THEN 
 UPDATE SET m.Phonematch = 1，datemodified = getdate（）
当不匹配时
 INSERT（DoeID，SatmarID，PhoneMatch ，Verified，DateCreated）
 VALUES（DOE.REG，id，1，（SELECT ID FROM MatchStatus where Status ='Not Verified'），GETDATE（））; 
  

错误：

 code> Msg 8672，级别16，状态1，行1 
 MERGE语句尝试多次UPDATE或DELETE同一行。当目标行匹配多个源行时，会发生这种情况。 MERGE语句不能多次UPDATE / DELETE目标表的同一行。优化ON子句以确保目标行最多匹配一个源行，或使用GROUP BY子句对源行进行分组。 
  

解决方案

您正在查找 合并

我将使用示例解释它

示例 DDL

  CREATE TABLE Employee 
（
 EmployeeID INTEGER PRIMARY KEY，
 EmployeeName VARCHAR（15）
）
 b 
 $ b CREATE TABLE EmployeeSalary 
（
 EmployeeID INTEGER，
 EmployeeSalary INTEGER 
）
  

---

员工样例DML

  INSERT INTO Employee 
 VALUES（1，'SMITH'）
 INSERT INTO Employee 
 VALUES（2，'ALLEN'）
 INSERT INTO Employee 
 VALUES 'JONES'）
 INSERT INTO Employee 
 VALUES（4，'MARTIN'）
 INSERT INTO Employee 
 VALUES（5，'JAMES'）
  

EmployeeDetails的示例DML

  INTO雇员薪金
价值（1,23000）
 INSERT INTO雇员薪金
价值（2,25500）
 INSERT INTO雇员薪金
价值（3,20000）
  

---

合并查询

  MERGE EmployeeSalary AS stm 
 USING（SELECT EmployeeID，EmployeeName FROM Employee）AS sd 
 ON stm.EmployeeID = sd.EmployeeID 
 UPDATE SET stm.EmployeeSalary = stm.EmployeeSalary + 12 
当不匹配时
 INSERT（EmployeeID，EmployeeSalary）
 VALUES（sd.EmployeeID，25000）; 
  
 
 
 
 
 
 
 参考 p> 
 
 
  首次参考  
 
 
 
  第二次参考  > 
 
 
  第三次参考  
 
 
 
  第四次参考  
 
I'm trying to find matches within two tables. first I'll compare the phone number then the last name etc. I'll insert the matches in a new table.

My question is first if it'll be faster and better if I do it directly in SQLServer or  in my .net program?

If SQL is better I need some help;

I want to check if that record exist in the match table, if it does it should only modify this record t specify that there's an other match.

I could do a separate insert and modify query for each type of match. I just wonder if there's a nicer way of doing it. 

I tried the merge, it worked to insert but on modify it gives me an error, please see below.
MERGE Matches AS M
USING (SELECT DOE.REG, sl.id FROM DOE INNER JOIN SL ON DOE.TEL = sl.phone) AS DOE
On M.doeid = DOE.reg
WHEN MATCHED THEN 
UPDATE SET m.Phonematch = 1, datemodified = getdate()
WHEN NOT MATCHED THEN
INSERT(DoeID, SatmarID, PhoneMatch , Verified, DateCreated)
VALUES(DOE.REG, id, 1, (SELECT ID FROM MatchStatus where Status = 'Not Verified'), GETDATE());
Error:
Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

解决方案
You are looking for Merge

I will explain it using an example

Sample DDL
CREATE TABLE Employee
(
   EmployeeID INTEGER PRIMARY KEY,
   EmployeeName VARCHAR(15)
)


CREATE TABLE EmployeeSalary
(
    EmployeeID INTEGER ,
    EmployeeSalary INTEGER
)




Sample DML For Employee

INSERT INTO Employee
VALUES(1,'SMITH')
INSERT INTO Employee
VALUES(2,'ALLEN')
INSERT INTO Employee
VALUES(3,'JONES')
INSERT INTO Employee
VALUES(4,'MARTIN')
INSERT INTO Employee
VALUES(5,'JAMES')


Sample DML For EmployeeDetails

INSERT INTO EmployeeSalary
VALUES(1,23000)
INSERT INTO EmployeeSalary
VALUES(2,25500)
INSERT INTO EmployeeSalary
VALUES(3,20000)




Merge Query

MERGE EmployeeSalary AS stm
USING (SELECT EmployeeID,EmployeeName FROM Employee) AS sd
ON stm.EmployeeID = sd.EmployeeID
WHEN MATCHED THEN UPDATE SET stm.EmployeeSalary = stm.EmployeeSalary + 12
WHEN NOT MATCHED THEN
INSERT(EmployeeID,EmployeeSalary)
VALUES(sd.EmployeeID,25000);




References

First Reference 

Second Reference

Third Reference

Fourth Reference

                        
这篇关于匹配2个表 - 然后插入或修改到新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！