|
View previous topic :: View next topic
|
| Author |
Message |
Achroo
New User
Joined: 07 Jan 2008
Posts: 7
Location: noida
|
|
|
|
Hi
Can we use update with join in any way.
I am currently using
Update Table_A Inner Join Table_B on Table_A(col1) = Table_B(col2)
Set Col_3 = some value Where some condition;
But I am getting SQLCode= -199 "Illegal use of word Inner" with SQLstate = 42601.
I have tried with only Inner and JOIN as well. but same SQL error code is coming.
Is there any way out?
Thanks.
Achroo |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Is there any way out? |
Not the way you are coding the sql. . .
If you post the actual requirement, someone may have a suggestion.
You should be able to do what you want without an inner/outer join. Something like:
| Quote: |
When you need to update one table based on the other table -
it is called "correlated update". You have to repeat the same "where"
logic 2 times.
| Code: |
update maintab m
set (m.fname, m.lname) =
(select u.fname,u.lname from updatetab u where m.id=u.id)
where exists
(select null from updatetab u where m.id=u.id);
or variation: use "in" expression:
update maintab m
set (m.fname, m.lname) =
(select u.fname,u.lname from updatetab u where m.id=u.id)
where m.id in
(select u.id from updatetab);
|
|
|
|
| Back to top |
|
|
Achroo
New User
Joined: 07 Jan 2008
Posts: 7
Location: noida
|
|
|
|
Hi
Basically I am running SQL query through SPUFI instead of program.
I am using JOIN instead of SUBQUERY beacuse it returns multiple rows.
Query1
SELECT B.COL1,B.COL2,C.COL2
FROM TABLE_1 B,TABLE_2 C
WHERE B.COL2 ¬= C.COL2
AND B.COL1 = C.COL1
AND C.COL3= '6'
AND C.COL4 = ' '
;
The above query is running correctly and returns n no. of rows.
Now I need to update value of COL2 of TABLE_1 with COL2 of TABLE_2.
Therefore I am using following Update query
UPDATE TABLE_2 C
INNER JOIN TABLE_1 B ON
B.COL2 ¬= C.COL2
AND B.COL1 = C.COL1
SET B.COL2 = C.COL2
WHERE C.COL3= '6'
AND C.COL4 = ' '
;
But I am getting SQLCode -199.
Thanks
Achroo |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Yes, because what you are doing is invalid. . . |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
UPDATE TABLE_1 B
SET B.COL2 = (select C.COL2 from TABLE_2 C
where B.COL1 = C.COL1
and B.COL2 ¬= C.COL2
and C.COL3= '6'
AND C.COL4 = ' ' )
where exists(select C.COL2 from TABLE_2 C
where B.COL1 = C.COL1
and B.COL2 ¬= C.COL2
and C.COL3= '6'
AND C.COL4 = ' ' ) |
this should work as long as there is maximum only one row in table_2 for each COL1, '6', ' '
otherwise you'll have to add min() or max() and group by, or extra criteria on table2 |
|
| Back to top |
|
|
cdhami
New User
Joined: 24 Jan 2006
Posts: 28
|
|
|
|
Hi Dick,
I have tried the query you mentioned..its not working for DB2 to update a table using data from another table.
UPDATE UU01.TLT_STD_LOROCOMB_S A SET
( A.SUBPAY_FLOW_CODE
,A.CHARGE_CODE_1
)
=
( SELECT B.SUBPAY_FLOW_CODE
,B.CHARGE_CODE_1
FROM UU01.TLT_STAN_LORO_COMB B
WHERE B.COMBINATION_ID = '001'
)
WHERE A.COMBINATION_ID ='001'
DSNT408I SQLCODE = -401, ERROR: THE OPERANDS OF AN ARITHMETIC OR COMPARISON
OPERATION ARE NOT COMPARABLE
DSNT418I SQLSTATE = 42818 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOBFC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 930 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000003A2' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Pls suggest
Thanks
Cdhami |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Is COMBINATION_ID character in both tables ?
Is CHARGE_CODE_1 of same type in both tables ?
Is SUBPAY_FLOW_CODE of same type in both tables ? |
|
| Back to top |
|
|
cdhami
New User
Joined: 24 Jan 2006
Posts: 28
|
|
|
|
hi
the query is working fine..
thx all |
|
| Back to top |
|
|
cdhami
New User
Joined: 24 Jan 2006
Posts: 28
|
|
|
|
the combination id was not having correct format ..its numeric..
thx |
|
| Back to top |
|
|
|
|
