Results 1 to 7 of 7

Thread: Update Statment

  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: Update Statment

    I am running DB2 8.1 fixpack 5 on a windows 2003 enterprise server.
    I am in the middle of a conversoin from SQL Server to DB2 and I need to rewrite some code.
    The original code is as follows:
    Code:
    update dbo.stg_joc_tbl 
    set office = s.office
    from dbo.stg_joc_tbl j, dbo.REF_NA_SALES_XREF_TBL s
    where j.district = s.district_code
    I have found that i cant use the from clause in an update statment for DB2. I have tried various forms of subselect statments with no avail. any help would be great.
    Jim

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Check for the syntax ... But, basically, I assume this will work

    update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and
    exists (
    select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
    )
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by sathyaram_s
    Check for the syntax ... But, basically, I assume this will work

    update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and
    exists (
    select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
    )

    Your code:
    Code:
    update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and 
    exists (
    select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
    )
    Produced the following error:
    Code:
    update db2admin.stg_joc_tbl j 
    Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and 
    exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district);
    then i thought that the parinthases were in the wrong spot so i ran the following code:
    Code:
    update db2admin.stg_joc_tbl j 
    Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.distict and 
    exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district));
    and got the following error
    Code:
    update db2admin.stg_joc_tbl j 
    Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.distict and 
    exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district));
    I have looked at the syntax in the documentation, but i havent seen anything that deals with anything this complicated. If you see it documented I would be happy to go read it if you tell me the chaperter/heading.
    thanks for all of your help
    Jim

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    hmm maybe i should be looking at the code beter. I didnt notice a few column names named improperly.
    Code:
    update db2admin.stg_joc_tbl j 
    Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.district_code and 
    exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.district=s1.district_code));
    This is running as we speak
    Thanks again for the help
    Jim

  5. #5
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Question Mass update

    Quote Originally Posted by JDionne
    hmm maybe i should be looking at the code beter. I didnt notice a few column names named improperly.
    Code:
    update db2admin.stg_joc_tbl j 
    Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.district_code and 
    exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.district=s1.district_code));
    This is running as we speak
    Thanks again for the help
    Jim

    Jim,

    I am trying to do the same thing you tried sometime ago. Were you able to do a mass update?

    Thanks in advance,

    Newbie

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by dsusendran
    Jim,

    I am trying to do the same thing you tried sometime ago. Were you able to do a mass update?

    Thanks in advance,

    Newbie
    This bit of code does the job for me
    Code:
    update db2admin.stg_joc_tbl j 
    Set office=(select s.office from db2admin.REF_NA_SALES_XREF_TBL s where j.district=s.district_code and 
    exists (select 1 from db2admin.REF_NA_SALES_XREF_TBL s1 where j.district=s1.district_code));
    Honestly Im still working on firguring out exactly what is going on here.
    I would go to this reference for a deeper understanding of the code.
    http://ourworld.compuserve.com/homep...l/DB2V81CK.PDF
    ------------
    And back to SQL Server....I always find my way home
    View my Linkedin profile

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Better late then never

    The update should have been

    update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) where
    exists (
    select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
    )


    Quote Originally Posted by sathyaram_s
    Check for the syntax ... But, basically, I assume this will work

    update dbo.stg_joc_tbl j set office=(select s.office from dbo.REF_NA_SALES_XREF_TBL s where j.district=s.distict) and
    exists (
    select 1 from dbo.REF_NA_SALES_XREF_TBL s1 where j.distict=s1.district
    )
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •