By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

UPDATE problem

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.