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...
Unconfigured Ad Widget
Collapse
Announcement
Collapse
No announcement yet.
Update with "order by" and "fetch first 1 rows only"
Collapse
X
-
Update with "order by" and "fetch first 1 rows only"
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))
Comment
-
Update with "order by" and "fetch first 1 rows only"
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
Code
Comment
-
Update with "order by" and "fetch first 1 rows only"
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 )
Comment
-
Update with "order by" and "fetch first 1 rows only"
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
Comment
-
Update with "order by" and "fetch first 1 rows only"
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...
Code
Comment
-
Update with "order by" and "fetch first 1 rows only"
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.
Comment
-
Update with "order by" and "fetch first 1 rows only"
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)
Code
Comment
-
Update with "order by" and "fetch first 1 rows only"
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.
Code
Comment
Comment