Modify data in the same table, the update statement writing problems-CodePudding

The table in figure 1 below,
According to jyls field to distinguish, then ywlxbm=xxyw001 LZJG field value inserted into ywlxbm=xxyw005 KPDXZH field
Figure 1 I write SQL can only on the basis of the first ywlxbm=xxyw001 jyls LZJG value, which corresponds to update the full table ywlxbm=xxyw005 KPDXZH field, the results of figure 2

How can we achieve the result of figure 3, please?

CodePudding user response:

 MERGE INTO the LS A 
USING (SELECT JYLS, LZJG 
The FROM LS 
B='XXYW001' WHERE YWLXBM) 
ON (a. YLS=B.J YLS) 
The WHEN MATCHED THEN 
The UPDATE 
The SET A.K PDXZH=B.L ZJG 
WHERE A.Y WLXBM='XXYW005';

CodePudding user response:

 update ls t1 
Set a t1. KPD=(. Select t2 LZJG from ls t2 where t1. Jyls=t2. Jyls and t2. Ywlxbm='xxyw001') 
Where a t1. Ywlxbm='xxyw005';

CodePudding user response:

reference 1st floor Mricoo_ weeks response:

 MERGE INTO the LS A 
USING (SELECT JYLS, LZJG 
The FROM LS 
B='XXYW001' WHERE YWLXBM) 
ON (a. YLS=B.J YLS) 
The WHEN MATCHED THEN 
The UPDATE 
The SET A.K PDXZH=B.L ZJG 
WHERE A.Y WLXBM='XXYW005';

Thank you very much! Solid basic skills don't go back to make up a missed lesson!