The MERGE to a deal with another batch of new data comparison table?-CodePudding

At present is that the bulk of new data, the first row and list A heavy, don't repeat is inserted INTO A table in A

 MERGE INTO A table USING existing 
(SELECT '1 nhxjrvkxvz2gear12in8aaxk69cae9l41' d 
UNION ALL SELECT '1 jb4sxtfwpdyw4o1abudfurcpsq4thjjfg' d 
UNION ALL SELECT '1 kveonm2cvpktpbmeykguheenwccfcyvjc' d 
UNION ALL SELECT '19 b1tyo63gfvzep85kq18vgpybznkqe72x' d) 
Add ON (=new existing. Address. D) the WHEN NOT MATCHED THEN INSERT (address) VALUES (new. D);

need to add A logic: assuming that receives the client to come over to 100 data, complete row behind 20 pairs of data preparation to be heavy to form A new, now will this article 20 record again and table B do the contrast, if records exist in B, to mark them A mark used in the future,

CodePudding user response:

Inside the new there add a left join table put together mark in b

CodePudding user response:

Probably mean it

 MERGE INTO A table USING existing 
(the SELECT temp1. D, b. marked * FROM (SELECT '1 nhxjrvkxvz2gear12in8aaxk69cae9l41' d 
UNION ALL SELECT '1 jb4sxtfwpdyw4o1abudfurcpsq4thjjfg' d 
UNION ALL SELECT '1 kveonm2cvpktpbmeykguheenwccfcyvjc' d 
UNION ALL SELECT '19 b1tyo63gfvzep85kq18vgpybznkqe72x' d) temp1 LEFT JOIN b ON temp1. Col=biggest ol) 
Add ON (=new existing. Address. D) the WHEN NOT MATCHED THEN INSERT (address, mark) VALUES (new. D, new. Mark);

CodePudding user response:

refer to the second floor February 16 response:

about this meaning

 MERGE INTO A table USING existing 
(the SELECT temp1. D, b. marked * FROM (SELECT '1 nhxjrvkxvz2gear12in8aaxk69cae9l41' d 
UNION ALL SELECT '1 jb4sxtfwpdyw4o1abudfurcpsq4thjjfg' d 
UNION ALL SELECT '1 kveonm2cvpktpbmeykguheenwccfcyvjc' d 
UNION ALL SELECT '19 b1tyo63gfvzep85kq18vgpybznkqe72x' d) temp1 LEFT JOIN b ON temp1. Col=biggest ol) 
Add ON (=new existing. Address. D) the WHEN NOT MATCHED THEN INSERT (address, mark) VALUES (new. D, new. Mark);

this order seems to be going to insert data first compared with B table, and then look for A table to heavy, isn't it? But this table B is very large, with A table to do first to heavy, to table A and B, to many,

CodePudding user response:

A table field: address (text), identification (logical)
B table fields: address (text)
Client sent a bunch of records, contains only address (text), want to accomplish:
First step: make difference set and A table first, to get rid of this pile of record already exists in A record of A table,
Records in the second part, the first step results in A table, but cent two kinds of circumstances, the first is not exist in table B, the second is B also have the record in the table, save the time difference is written in the book of the recognition field,

CodePudding user response:

 
Insert into A table (address, recognition) 
Select the address=t.d, 
Identify=case when the exists (select 1 from table B where b. B address=t.d) then 1 
The else 0 end 
The from (select d='1 nhxjrvkxvz2gear12in8aaxk69cae9l41' union all 
Select d='1 jb4sxtfwpdyw4o1abudfurcpsq4thjjfg' union all 
Select d='1 kveonm2cvpktpbmeykguheenwccfcyvjc' union all 
Select d='19 b1tyo63gfvzep85kq18vgpybznkqe72x) t 
Left the join A table A on t.d=a. address 
Where a. The address is null

CodePudding user response:

will now this article 20 record again and table B do the contrast, if records exist in B, to mark them a mark used in the future,

Could come in the data to write, writing down the mark,