Pray god help me, how do you write the SQL-CodePudding

Raw data:
Id para acode fina
1111 2222 X1
1112 3333 X2
1113 4444 X3
1114 2222 X4
1115 2222 X5
2222 1234 1245 4578
3333 0000 12354 3695
3333 0000 12354 3695
1116 4251 4453 44524
Update logic:
1. If acode begin with X, then put the para as the id to find the original table
2. To find out 1 fina to update the original table
The changed data
Id para acode fina
1111 2222 X1 4578
1112 3333 X2 3695
1113 4444 X3 8527
1114 2222 X4 4578
1115 2222 x 5 4578
2222 1234 1245 4578
3333 0000 12354 3695
4444 1235 3698 8527
1116 4251 4453 44524
Great god see, batch updates in an update statement

CodePudding user response:

Update the table a set fina=(select b. ina from table b where Amy polumbo ara=b.i d)
Where fina is null and substr (acode, 0, 1)='X'

CodePudding user response:

Quote it again?

CodePudding user response:

The accurate point

Update the table a set fina=(select b. ina from table b where Amy polumbo ara=b.i d and substr (acode, 0, 1) & lt;> 'X' and fina is not null)
Where fina is null and substr (acode, 0, 1)='X'

CodePudding user response:

 
SQL> Select * from t4; 
ID PARA ACODE FINA 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1111 2222 X1 
1112 3333 X2 
1113 4444 X3 
1114 2222 X4 
1115 2222 X5 
2222 1234 1245 4578 
3333 0000 12354 3695 
4444 1235 3698 8527 
1116 4251 4453 44524 
9 rows selected 

SQL> 
SQL> The update (
The select/* 2 + BYPASS_UJVC */b. ina finab, a.f ina finaa from t4 b, 
3 (select para, fina the from t4 where substr (acode, 1, 1)='X') a 
4 where Amy polumbo ara=b.i d) 
5 set finaa=finab 
6; 
5 rows updated 

SQL> commit; 
Commit complete 

SQL> Select * from t4; 
ID PARA ACODE FINA 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1111 2222 X1 4578 
1112 3333 X2 3695 
1113 4444 X3 8527 
1114 2222 X4 4578 
1115 2222 x 5 4578 
2222 1234 1245 4578 
3333 0000 12354 3695 
4444 1235 3698 8527 
1116 4251 4453 44524 
9 rows selected

I prefer this way

CodePudding user response:

4th floor, which is in the db2 authentication?

CodePudding user response:

reference 5 floor sinat_19562099 reply:

4th floor, which is in the db2 authentication?

Another kind of writing, aniseed Hui has several kinds of writing

CodePudding user response:

The update t0 t1
The set of fina=(
Select t2. Fina the from t0 t2 where t2. Id=t1. Para and t1. Acode like 'X % and t1. The fina is null
)
Where fina is null and acode like 'X %'