. There are two same table T1, T2, field Code is contained in table, ByName, ObjectName, etc. The other fields, now I want to contrast two table ObjectName, ByName of deposit Code value in T1, T2, if T1 ByName has value, and the Code is not the same with T1, the newly built a record, the description is not very clear, draw a diagram to show it,

T1
Code, ByName, ObjectName,,,
Aa 0 rubber,,,
Bb 0 pencil,,,
Cc 0 ruler,,,

T2
Code, ByName, ObjectName,,,
AA AA rubber,,,
BB BB pencil,,,
CC 0 ruler,,,

Finally I hope to get the T2 like this:
Code, ByName, ObjectName,,,
AA AA rubber,,, (ByName is not null)
AA AA rubber,,, (add a record, the other field values keep up with the record below the same)
BB BB pencil,,,
BB BB pencil,,,
CC CC ruler,,, (ByName is empty, direct changes)

Hope all eight drought on you support a move to god, god first thanked you,

CodePudding user response:

 WITH T1 AS 
(SELECT "aa" CODE, '0' BYNAME, 'rubber' OBJECTNAME 
The FROM DUAL 
UNION ALL 
SELECT the 'bb', '0', the 'pencil' 
The FROM DUAL 
UNION ALL 
SELECT the 'cc', '0', 'ruler' 
The FROM DUAL), 
T2 AS 
(SELECT "AA" CODE, 'AA' BYNAME, 'rubber' OBJECTNAME 
The FROM DUAL 
UNION ALL 
SELECT the 'BB', 'BB', 'pencil' 
The FROM DUAL 
UNION ALL 
SELECT the 'CC', '0', 'ruler' 
The FROM DUAL) 
The SELECT NVL (A.C ODE, Arthur c. ODE) AS CODE, 
NVL (NULLIF (a. YNAME, '0'), the biggest ODE) AS BYNAME, 
NVL (A.O BJECTNAME, B.O BJECTNAME) AS OBJECTNAME 
The FROM T2 A 
FULL JOIN T1 B 
ON A.O BJECTNAME=B.O BJECTNAME 
AND (a. YNAME=B.B YNAME OR a. YNAME='0') 
LEFT the JOIN T2 C 
ON C.O BJECTNAME=B.O BJECTNAME 
The ORDER BY CODE, BYNAME 

CodePudding user response:

 MERGE INTO T2 A 
USING (SELECT T1. *, DECODE (T2) BYNAME, '0', 1, 0) AS the FLAG, T2. The CODE AS NEW_CODE 
The FROM T1 
LEFT the JOIN T2 
ON T2. OBJECTNAME=T1. OBJECTNAME 
AND (T1) BYNAME & lt;> T2) BYNAME OR T2) BYNAME='0')) 
 BON (A.O BJECTNAME=B.O BJECTNAME AND b. LAG=1) 
The WHEN MATCHED THEN 
The UPDATE 
The SET a. YNAME=biggest ODE 
WHEN NOT MATCHED THEN 
INSERT 
VALUES 
(B.N EW_CODE, biggest ODE, B.O BJECTNAME) 

Update the SQL

CodePudding user response:

refer to the second floor chengccy response:

 MERGE INTO T2 A 
USING (SELECT T1. *, DECODE (T2) BYNAME, '0', 1, 0) AS the FLAG, T2. The CODE AS NEW_CODE 
The FROM T1 
LEFT the JOIN T2 
ON T2. OBJECTNAME=T1. OBJECTNAME 
AND (T1) BYNAME & lt;> T2) BYNAME OR T2) BYNAME='0')) 
 BON (A.O BJECTNAME=B.O BJECTNAME AND b. LAG=1) 
The WHEN MATCHED THEN 
The UPDATE 
The SET a. YNAME=biggest ODE 
WHEN NOT MATCHED THEN 
INSERT 
VALUES 
(B.N EW_CODE, biggest ODE, B.O BJECTNAME) 

Update SQL

The god, thank you for your busy schedule to give me reply, can you don't have to merge to realize, I use is to Access database,

CodePudding user response:

reference zyy2010 reply: 3/f

Quote: refer to the second floor chengccy response:

 MERGE INTO T2 A 
USING (SELECT T1. *, DECODE (T2) BYNAME, '0', 1, 0) AS the FLAG, T2. The CODE AS NEW_CODE 
The FROM T1 
LEFT the JOIN T2 
ON T2. OBJECTNAME=T1. OBJECTNAME 
AND (T1) BYNAME & lt;> T2) BYNAME OR T2) BYNAME='0')) 
 BON (A.O BJECTNAME=B.O BJECTNAME AND b. LAG=1) 
The WHEN MATCHED THEN 
The UPDATE 
The SET a. YNAME=biggest ODE 
WHEN NOT MATCHED THEN 
INSERT 
VALUES 
(B.N EW_CODE, biggest ODE, B.O BJECTNAME) 

Update SQL

The god, thank you for your busy schedule to give me reply, can you don't have to merge to realize, I use is to Access database,

Access is old, not if you just write a few SQL execution,

CodePudding user response:

reference 4 floor sxq129601 response:

Quote: reference zyy2010 reply: 3/f

Quote: refer to the second floor chengccy response:

 MERGE INTO T2 A 
USING (SELECT T1. *, DECODE (T2) BYNAME, '0', 1, 0) AS the FLAG, T2. The CODE AS NEW_CODE 
The FROM T1 
LEFT the JOIN T2 
ON T2. OBJECTNAME=T1. OBJECTNAME 
AND (T1) BYNAME & lt;> T2) BYNAME OR T2) BYNAME='0')) 
 BON (A.O BJECTNAME=B.O BJECTNAME AND b. LAG=1) 
The WHEN MATCHED THEN 
The UPDATE 
The SET a. YNAME=biggest ODE 
WHEN NOT MATCHED THEN 
INSERT 
VALUES 
(B.N EW_CODE, biggest ODE, B.O BJECTNAME) 

Update SQL

The god, thank you for your busy schedule to give me reply, can you don't have to merge to realize, I use is to Access database,

Access is old, not if you just write a few SQL execution,

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull