could i update with a unl file

Posted in 2012

Summary

The poster asked whether rows in an existing table can be updated from a .unl unload file, the way dbload can insert from one. Art Kagel explained dbload only inserts, and suggested mapping the .unl file with CREATE EXTERNAL TABLE (datafiles("disk:/path/file.unl"), delimiter="|") and then running an UPDATE whose SET and WHERE clauses select from that external table, matching on a key column. Fernando Nunes suggested MERGE as an alternative, and another reply suggested loading the .unl into a temp table and updating from there, or using ACE to build the file. The poster said he would try it but reported no result.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

Juan Francisco González Navarro — Thu, 11 Oct 2012 19:20:49 — source: IIUG Forums & Mailing Lists

Hello: I'd like to update some rows with an unl file. Is there any way to do it? Regards. --005045015cb8ecdb6604cbcd7752

↪ replying to Juan Francisco González Navarro

Art Kagel — Thu, 11 Oct 2012 19:24:56 — source: IIUG Forums & Mailing Lists

WIth a text editor!?! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:20 PM, Juan Francisco González Navarro < jfrancisco.navarro@gmail.com> wrote: > Hello: > > I'd like to update some rows with an unl file. Is there any way to do it? > > Regards. > > --005045015cb8ecdb6604cbcd7752 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba614c809944c104cbcd8678

↪ replying to Art Kagel

Juan Francisco González — Thu, 11 Oct 2012 19:31:19 — source: IIUG Forums & Mailing Lists

Hi: Maybe my question is not clear. My doubt is about lo update rows with dbload in the same way we can do a insert. regards. 2012/10/11 Art Kagel <art.kagel@gmail.com> > WIth a text editor!?! > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:20 PM, Juan Francisco González Navarro < > jfrancisco.navarro@gmail.com> wrote: > > > Hello: > > > > I'd like to update some rows with an unl file. Is there any way to do it? > > > > Regards. > > > > --005045015cb8ecdb6604cbcd7752 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba614c809944c104cbcd8678 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b6d89b25b397c04cbcd9de3

↪ replying to Juan Francisco González

Art Kagel — Thu, 11 Oct 2012 19:41:20 — source: IIUG Forums & Mailing Lists

Ahh, different question. Sorry. You can do this my mapping an EXTERNAL TABLE to the .unl file the update the target table's rows using SQL selecting the data from the external table. create external table unload_file_table( <columns defs> ) using ((datafiles("disk:/path/to/unload_file.unl"), delimiter="|"); update target_table set target_table.some_column = (select some_column from unload_file_table as uft where uft.keycol = target_table.keycol) and keycol in (select keycol from unload_file_table); Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:31 PM, Juan Francisco González Navarro < jfrancisco.navarro@gmail.com> wrote: > Hi: > > Maybe my question is not clear. > > My doubt is about lo update rows with dbload in the same way we can do a > insert. > > regards. > > 2012/10/11 Art Kagel <art.kagel@gmail.com> > > > WIth a text editor!?! > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, > > implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:20 PM, Juan Francisco González Navarro < > > jfrancisco.navarro@gmail.com> wrote: > > > > > Hello: > > > > > > I'd like to update some rows with an unl file. Is there any way to do > it? > > > > > > Regards. > > > > > > --005045015cb8ecdb6604cbcd7752 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --90e6ba614c809944c104cbcd8678 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7b6d89b25b397c04cbcd9de3 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340747ceaf4004cbcdc0ee

↪ replying to Art Kagel

Fernando Nunes — Thu, 11 Oct 2012 21:10:34 — source: IIUG Forums & Mailing Lists

Merge...? Not sure if there is any restriction for external tables? On Oct 11, 2012 8:41 PM, "Art Kagel" <art.kagel@gmail.com> wrote: > Ahh, different question. Sorry. You can do this my mapping an EXTERNAL > TABLE to the .unl file the update the target table's rows using SQL > selecting the data from the external table. > > create external table unload_file_table( <columns defs> ) using > ((datafiles("disk:/path/to/unload_file.unl"), delimiter="|"); > > update target_table > set target_table.some_column = (select some_column from unload_file_table > as uft where uft.keycol = target_table.keycol) > > and keycol in (select keycol from unload_file_table); > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:31 PM, Juan Francisco González Navarro < > jfrancisco.navarro@gmail.com> wrote: > > > Hi: > > > > Maybe my question is not clear. > > > > My doubt is about lo update rows with dbload in the same way we can do a > > insert. > > > > regards. > > > > 2012/10/11 Art Kagel <art.kagel@gmail.com> > > > > > WIth a text editor!?! > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor > any > > > other organization with which I am associated either explicitly, > > > implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:20 PM, Juan Francisco González Navarro < > > > jfrancisco.navarro@gmail.com> wrote: > > > > > > > Hello: > > > > > > > > I'd like to update some rows with an unl file. Is there any way to do > > it? > > > > > > > > Regards. > > > > > > > > --005045015cb8ecdb6604cbcd7752 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --90e6ba614c809944c104cbcd8678 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --047d7b6d89b25b397c04cbcd9de3 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340747ceaf4004cbcdc0ee > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b6d89b251fbf604cbcf008d

↪ replying to Art Kagel

Juan Francisco González — Fri, 12 Oct 2012 07:21:38 — source: IIUG Forums & Mailing Lists

hello thank you. i'm going to try El 11/10/2012 21:41, "Art Kagel" <art.kagel@gmail.com> escribió: > Ahh, different question. Sorry. You can do this my mapping an EXTERNAL > TABLE to the .unl file the update the target table's rows using SQL > selecting the data from the external table. > > create external table unload_file_table( <columns defs> ) using > ((datafiles("disk:/path/to/unload_file.unl"), delimiter="|"); > > update target_table > set target_table.some_column = (select some_column from unload_file_table > as uft where uft.keycol = target_table.keycol) > > and keycol in (select keycol from unload_file_table); > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:31 PM, Juan Francisco González Navarro < > jfrancisco.navarro@gmail.com> wrote: > > > Hi: > > > > Maybe my question is not clear. > > > > My doubt is about lo update rows with dbload in the same way we can do a > > insert. > > > > regards. > > > > 2012/10/11 Art Kagel <art.kagel@gmail.com> > > > > > WIth a text editor!?! > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor > any > > > other organization with which I am associated either explicitly, > > > implicitly, or by inference. 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 Thu, Oct 11, 2012 at 3:20 PM, Juan Francisco González Navarro < > > > jfrancisco.navarro@gmail.com> wrote: > > > > > > > Hello: > > > > > > > > I'd like to update some rows with an unl file. Is there any way to do > > it? > > > > > > > > Regards. > > > > > > > > --005045015cb8ecdb6604cbcd7752 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --90e6ba614c809944c104cbcd8678 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --047d7b6d89b25b397c04cbcd9de3 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340747ceaf4004cbcdc0ee > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3074b3e08e42f604cbd7897c

↪ replying to Juan Francisco González Navarro

FRANK J. COMPUTER — Fri, 19 Oct 2012 22:44:43 — source: IIUG Forums & Mailing Lists

1. Load the unl back into an Informix temp table, update it, then unload again. 2. Use ACE report writer to create a customized unload file.