Update with "order by" and "fetch first 1 rows only"

[K.Forsythe]

I did not get a chance to test the whole thing but try this... update test1 a set a.zona12 = (select coalesce(b.zona22,a.zona12) from test2 b where a.zona11 = b.zona21 AND b.zona23 = (select Max(c.zona23) from test2 c where a.zona21 = c.zona21 and b.zona22=c.zona22)) Let me know how that works out...

[Guest.Visitor]

"Result of SELECT more than one row"...

[K.Forsythe]

try... update test1 a set a.zona12 = (select coalesce(b.zona22,a.zona12) from test2 b where a.zona11 = b.zona21 AND b.zona23 = (select Max(c.zona23) from test2 c where a.zona21 = c.zona21 and b.zona22=c.zona22) fetch first 1 rows only) or update test1 a set a.zona12 = (select coalesce(min(b.zona22) ,a.zona12) from test2 b where a.zona11 = b.zona21 AND b.zona23 = (select Max(c.zona23) from test2 c where a.zona21 = c.zona21 and b.zona22=c.zona22))

[Guest.Visitor]

thank you - i tried them both. the first one says "Keyword FETCH not expected" (same problem as with my query), and the second one doesn't update the file as I want. i'm not good in explaining what i want, so i just wrote a little program that does the job (code below) - but I still want to use SQL instead :) <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b379875/4')

[K.Forsythe]

I'll take a look at this when I have more time to recreate your file structure and get you a tested syntax. Sorry for not having it right (yet).

[Guest.Visitor]

thank you for your time - of course it's not an urgent matter. My real problem is that I'm not allowed to put RPG code in production (I only use it to make tools in the development environment), as the "official" programming language of the company where I am in this moment is Cobol. And I HATE writing Cobol code after getting used to RPG :))

[K.Forsythe]

OK not that I am biased or anything.... But COBOL? I admit 15 years ago RPG was not THAT much better than COBOL. But RPG has evolved since then. And COBOL... What exactly is the draw?

[Guest.Visitor]

this box uses an application written a long time ago (maybe a little less than 15 years :)) and since then they bring only consultants that are familiar with Cobol. so I'm the only RPG programmer among 10 Cobol developers that don't understand RPG :) maybe I should just leave :)

[K.Forsythe]

[Guest.Visitor]

I agree with the principle, but Cobol doesn't give me much appetite :)) Otherwise, I didn't built well my example, sorry for that (it's 23:35 here in France...). The unique key on the second file is ZONE21 & ZONE23. I post below the complete and correct info (2 files & 1 program), waiting for the replacement of these 16 lignes of code with one SQL statement, rather than 100 lines of Cobol code... <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b379875/10')

[K.Forsythe]

Try... update test1 a set a.zona12 = (select coalesce(Max(b.zona22),a.zona12) from test2 b where a.zona11 = b.zona21 and b.zona23 = (select max(c.zona23) from Test2 C Where B.zona21 = C.zona21)) It worked for me with the sample data I had in the files.

[Guest.Visitor]

thank you, it does what I needed! I am going to test this at work on monday with the real files (pretty large ones) to see how much resources it takes.

[Guest.Visitor]

This was a hard one and I tried building it in pieces: 1) SELECT MAX(C.ZONA23) FROM TEST2 AS C GROUP BY C.ZONA21 2) SELECT MIN(B.ZONA22) AS MINZONA22 FROM TEST2 AS B WHERE (B.ZONA23 = (SELECT MAX(C.ZONA23) AS MAXZONA23 FROM TEST2 AS C WHERE (C.ZONA21 = B.ZONA21) GROUP BY C.ZONA21)) GROUP BY B.ZONA21 3) UPDATE TEST1 SET ZONA12 = (SELECT MIN(B.ZONA22) FROM TEST2 AS B WHERE ((B.ZONA21 = ZONA11) AND (B.ZONA23 = (SELECT MAX(C.ZONA23) FROM TEST2 AS C WHERE (C.ZONA21 = B.ZONA21) GROUP BY C.ZONA21))) GROUP BY B.ZONA21) 4) UPDATE TEST1 SET ZONA12 = COALESCE((SELECT MIN(B.ZONA22) FROM TEST2 AS B WHERE ((B.ZONA21 = ZONA11) AND (B.ZONA23 = (SELECT MAX(C.ZONA23) FROM TEST2 AS C WHERE (C.ZONA21 = B.ZONA21) GROUP BY C.ZONA21))) GROUP BY B.ZONA21), NULL) <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b379875/13')

[Guest.Visitor]

thank you, BXSGolfer! obviously, the solution is not at fingertips by SQL... at least not for me.

[Guest.Visitor]

Hi everybody, Here's an example of what I want to do: I have 2 files, TEST1 and TEST2 (with the structures pasted below) and I need to update the column ZONA12 of the first file with the value of the zone ZONA22 of the second file that corresponds to the maximum value of the zone ZONA23 of the second file. I tried this and SQL doesn't accept the syntax: update test1 a set a.zona12 = coalesce( (select b.zona22 from test2 b where a.zona11 = b.zona21 order by b.zona23 desc fetch first 1 rows only ), a.zona12 ) Can this somehow be done by one SQL command, or do I need to write a program only for this little update? :) Thanks in advance. <hr width=50 align=left>Code ('http://www.mcpressonline.com/mc/showcode@@.6b379875')

[Guest.Visitor]

It wasn't easy, so I can see your difficulty. I hope it helped.