Update query question

Posted in 2009

DON CHADWICK — Tue, 06 Oct 2009 21:12:50 — source: IIUG Forums & Mailing Lists

Hello All, I'm stumped on this one, hopefully someone can help. I have 2 temp tables. I want to take info from 1 and update rows in the other based on matching columns in both. Example: temp1 ColA ColB ColC ColD temp2 ColA ColB ColY ColZ I want to use the data in Cols Y and Z from temp2 and update Cols C and D in temp1 where Cols A and B in both tables match. Hopefully, that makes sense. Thanks. Don

↪ replying to DON CHADWICK

Art Kagel — Tue, 06 Oct 2009 21:22:55 — source: IIUG Forums & Mailing Lists

update temp1 set colC = (select colY from temp2 where temp2.colA = temp1.colA and temp2.colB = temp1.colB), colD = (select colZ from temp2 where temp2.colA = temp1.colA and temp2.colB = temp1.colB) where exists (select 1 from temp2 where temp2.colA = temp1.colA and temp2.colB = temp1.colB); Ugly but it should work. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Oct 6, 2009 at 5:12 PM, DON CHADWICK <d.l.chadwick@gmail.com> wrote: > Hello All, > I'm stumped on this one, hopefully someone can help. > I have 2 temp tables. I want to take info from 1 and update rows in the > other > based on matching columns in both. > Example: > temp1 > ColA ColB ColC ColD > > temp2 > ColA ColB ColY ColZ > > I want to use the data in Cols Y and Z from temp2 and update Cols C and D > in > temp1 where Cols A and B in both tables match. > > Hopefully, that makes sense. > Thanks. > Don > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bda2830867204754ad59e

↪ replying to Art Kagel

Jonathan Leffler — Wed, 07 Oct 2009 02:47:55 — source: IIUG Forums & Mailing Lists

On Tue, Oct 6, 2009 at 14:22, Art Kagel <art.kagel@gmail.com> wrote: > update temp1 > set colC = (select colY from temp2 where temp2.colA = temp1.colA and > temp2.colB = temp1.colB), > > colD = (select colZ from temp2 where temp2.colA = temp1.colA and > temp2.colB = temp1.colB) > where exists (select 1 from temp2 where temp2.colA = temp1.colA and > temp2.colB = temp1.colB); > > Ugly but it should work. > And only marginally less ugly - I think you can do: UPDATE temp1 SET (colC, colD) = ((select colY, colZ from temp2 where temp2.colA = temp1.colA and temp2.colB = temp1.colB)) WHERE EXISTS (select 1 from temp2 where temp2.colA = temp1.colA and temp2.colB = temp1.colB); I seem to remember the double brackets (parentheses) being necessary magic. > On Tue, Oct 6, 2009 at 5:12 PM, DON CHADWICK <d.l.chadwick@gmail.com> > wrote: > > I'm stumped on this one, hopefully someone can help. > > I have 2 temp tables. I want to take info from 1 and update rows in the > > other based on matching columns in both. > > Example: > > temp1 > > ColA ColB ColC ColD > > > > temp2 > > ColA ColB ColY ColZ > > > > I want to use the data in Cols Y and Z from temp2 and update Cols C and D > > in temp1 where Cols A and B in both tables match. > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Samuel Goldwyn<http://www.brainyquote.com/quotes/authors/s/samuel_goldwyn.html>; - "I'm willing to admit that I may not always be right, but I am never wrong." --00c09f97220aa13ca504754f5f7a

↪ replying to Jonathan Leffler

DON CHADWICK — Fri, 09 Oct 2009 20:24:34 — source: IIUG Forums & Mailing Lists

Awesome, thanks guys. I tried both versions and each worked. I will report that Jonathon's worked quite a bit faster, most likely due to the fact that the actual query wound up updating 5 columns. Still took 20 minutes to update 24,000 rows though. So, I am wondering if you think putting any sort of index on the temp tables would be worthwhile. Or would that slow down the initial creation of the tables to such an extent that it wouldn't really matter. Thanks again for the feedback. VERY useful. Don