468,242 Members | 1,591 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

UPDATE problem

Hallo all,

I'm quite new to SQL and I have a problem updating some fields in a table,
maybe some of you in this forum can help me!

This SELECT statement returns the records that I need to update:

select * from A, B
where A.a = 'D' and A.b = 'D '
and A.c = B.c and A.d = B.d
and B.a = '*' and B.b = '*'

The key for the two files is in fields .c and .d but I need to check also
BOTH fields B.a and B.b
because they are my selection flags... and they're not present in file A.

Now I would like to convert this SELECT in an UPDATE as I need to update
two of the fields in file A but this statement doesn't work ... ... !

UPDATE A, B
set A.a = 'X', A.b = 'X'
where A.a = 'D' and A.b = 'D '
and A.c = B.c and A.d = B.d
and B.a = '*' and B.b = '*'

It seems that UPDATE can manage only one table... Which is the solution?
If there isn't a solution with SQL I'll have to write a program to do it!

Best regards to all, Andrea
Nov 12 '05 #1
3 1551
Andrea,

UPDATE A
SET A.a = 'X', A.b = 'X'
WHERE A.a = 'D' and A.b = 'D '
AND (A.c, A.d) IN (SELECT B.c, B.d
FROM B WHERE B.a = '*' and B.b = '*');

There are also solutions using EXISTS.
The newest SQL Standard (and DB2 UDB for LUW) supports a MERGE statement
which you can sue for more complex scenarios.

Cheers
Serge
Nov 12 '05 #2
Hallo Serge,

first really thanks for your help, I've been able to understand
what to do even if I had to modify it.

My AS/400 v5r1 doesn't like the AND (A.c, A.d) IN .... line !

So, just for your information, I changed it like this (after MANY try):

update ORCLR01L A
SET A.ocmagr = 'T', A.ocfilr = 'T ', A.ocnumr = A.ocnumr + 42000
WHERE A.occlir = '008137' and A.ocansr = 2004
AND A.ocmagr IN (SELECT ocmagt FROM ORCLT01L B
WHERE B.ocstat = ' ' and B.ocrept = ' ' and B.occlit = '008137')
AND A.ocfilr IN (SELECT ocfilt FROM ORCLT01L B
WHERE B.ocstat = ' ' and B.ocrept = ' ' and B.occlit = '008137')
AND A.ocnumr IN (SELECT ocnumt FROM ORCLT01L B
WHERE B.ocstat = ' ' and B.ocrept = ' ' and B.occlit = '008137')

I think it's a bit redundant but... it worked.
From the manuals I could't figure out how to write the
AND (A.c, A.d) statement as there was always an error for the
comma or anything else after the first field...

Thank you very much and Ciao, Andrea

"Serge Rielau" <sr*****@ca.eye-bee-em.com> ha scritto nel messaggio
news:sP***************@news04.bloor.is.net.cable.r ogers.com...
Andrea,

UPDATE A
SET A.a = 'X', A.b = 'X'
WHERE A.a = 'D' and A.b = 'D '
AND (A.c, A.d) IN (SELECT B.c, B.d
FROM B WHERE B.a = '*' and B.b = '*');

There are also solutions using EXISTS.
The newest SQL Standard (and DB2 UDB for LUW) supports a MERGE statement
which you can sue for more complex scenarios.

Cheers
Serge

Nov 12 '05 #3
Try EXISTS then. Seems like your version doesn't understand the "row"
wise IN yet.

Cheers
Serge
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by al | last post: by
13 posts views Thread by abdoly | last post: by
8 posts views Thread by Zorpiedoman | last post: by
5 posts views Thread by =?Utf-8?B?UlBhcmtlcg==?= | last post: by
11 posts views Thread by SAL | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.