The Oracle database merge into the duplicate data insert-CodePudding

Now there was a very spirit of... In the database using the SQL statements, no matter how many times to perform do not appear duplicate data, but in the actual project, circulation read data, execute SQL, insert or update, there will be exactly the same as the duplicate data, statement, build table SQL without any constraints, can build table, to add a primary key constraints and so on, a blue thin to turn down this is what problem, need to add any constraint?

The merge into AA_Temp_gj t1 using dual on (
(select count (*) from AA_Temp_gj h where h.I D=101) & gt; 0)

The when matched then
Update the set ID=101, dcreatesystime=to_date (' 2020-06-23 16:36:11 ', '- dd yyyy - mm hh24: mi: ss'), cOperator='0222', cCheckMan='0222',
iAmount_f=200000.0000,Dverifydate=to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss'), iAmount=200000.0000, cDeptCode='01', cPerson='0102', cCusVen='230007', cSSCode='7', cBankAccount='0406000200035158',
DVouchDate=to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss'), cDigest='test data', cDepCode='null', cPersonCode=2010731, iAmt=200000.0000, iAmt_f=200000.0000, cBank='null',
CNatBankAccount='12345678901234567', cOrderID='null', cNoteNo='null', cVouchID='0000109' where id=101

When not matched then
Insert (ID, dcreatesystime cOperator, cCheckMan, iAmount_f, dverifydate, iAmount, cDeptCode, cPerson, cCusVen, cSSCode, cBankAccount, dVouchDate, cDigest, cDepCode, cPersonCode, iAmt,
IAmt_f cBank, cNatBankAccount cOrderID, cNoteNo cVouchID) values (101, to_date (' 16:36:11 2020-06-23 ', '- dd yyyy - mm hh24: mi: ss'), '0222', '0222', 200000.0000,
To_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss'), 200000.0000, '01', '0102', '230007', '7', '0406000200035158', to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss'), 'test data',
Null, 2010731200, 000.0000, 200000.0000, 'null', '12345678901234567', null, null, '0000109')

CodePudding user response:

1, never see the merge, and dual use, so long to see,
2, there should be concurrent, and concurrent or different cycle with the same ID value of the same session,

CodePudding user response:

Ok, thank you, also is the first time I use the merge

, do not too will use,

CodePudding user response:

 
CREATE UNIQUE INDEX idx_aa_temp_gj_u1 ON AA_Temp_gj (ID); 

The MERGE INTO AA_Temp_gj t1 
USING dual 
ON (t1) ID=101) 
The WHEN MATCHED THEN 
The UPDATE 
SET dcreatesystime=to_date (' 16:36:11 2020-06-23 ', '- dd yyyy - mm hh24: mi: ss') 
='0222', cOperator 
='0222', cCheckMan 
, iAmount_f=200000.0000 
, dverifydate=to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
, iAmount=200000.0000 
, cDeptCode='01' 
='0102', cPerson 
='230007', cCusVen 
, cSSCode='7' 
='0406000200035158', cBankAccount 
, dVouchDate=to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
, cDigest='test data' 
, cDepCode='null' 
, cPersonCode=2010731 
, iAmt=200000.0000 
, iAmt_f=200000.0000 
, cBank='null' 
='12345678901234567', cNatBankAccount 
, cOrderID='null' 
, cNoteNo='null' 
='0000109', cVouchID 
WHERE id=101 
WHEN NOT MATCHED THEN 
INSERT 
(ID 
, dcreatesystime 
, cOperator 
, cCheckMan 
, iAmount_f 
, dverifydate 
, iAmount 
, cDeptCode 
, cPerson 
, cCusVen 
, cSSCode 
, cBankAccount 
, dVouchDate 
, cDigest 
, cDepCode 
, cPersonCode 
, iAmt 
, iAmt_f 
, cBank 
, cNatBankAccount 
, cOrderID 
, cNoteNo 
, cVouchID) 
VALUES 
(101 
, to_date (' 16:36:11 2020-06-23 ', '- dd yyyy - mm hh24: mi: ss') 
, '0222' 
, '0222' 
, 200000.0000 
, to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
, 200000.0000 
The '01' 
, '0102' 
, '230007' 
, '7' 
, '0406000200035158' 
, to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
, 'test data' 
The NULL 
2010731 
, 200000.0000 
, 200000.0000 
, 'null' 
, '12345678901234567' 
The NULL 
The NULL 
, '0000109');

CodePudding user response:

The

reference 3 floor riven2011 response:

 
CREATE UNIQUE INDEX idx_aa_temp_gj_u1 ON AA_Temp_gj (ID); 

The MERGE INTO AA_Temp_gj t1 
USING dual 
ON (t1) ID=101) 
The WHEN MATCHED THEN 
The UPDATE 
SET dcreatesystime=to_date (' 16:36:11 2020-06-23 ', '- dd yyyy - mm hh24: mi: ss') 
='0222', cOperator 
='0222', cCheckMan 
, iAmount_f=200000.0000 
, dverifydate=to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
, iAmount=200000.0000 
, cDeptCode='01' 
='0102', cPerson 
='230007', cCusVen 
, cSSCode='7' 
='0406000200035158', cBankAccount 
, dVouchDate=to_date (' 2020-06-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
, cDigest='test data' 
, cDepCode='null' 
, cPersonCode=2010731 
, iAmt=200000.0000 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull