Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    35

    Unanswered: Update using joins

    I need to perform a update but not able to figure out in DB2


    Code:
    update TABLE A 
    set B.COL = 6794
    from TABLE B
    where A.id = B.id 
    and B.NAME = 'ABC'
    and date(B.STARTDATE) = date (current date)

  2. #2
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    Try this

    update TABLE B
    set B.COL = 6794
    where B.id IN (select id from TABLE A)
    and B.NAME = 'ABC'
    and date(B.STARTDATE) = date (getdate())

  3. #3
    Join Date
    May 2008
    Posts
    35
    What ? I said table a needs to be updated ?

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dinjo jo, Your statement is confusing, You have UPDATE TABLE A but it is followed by SET B.COL = 6794. It is not easy to tell which Table you were trying to Update.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    either way, you would still write your sql in the manner above just switch your tables around.
    Dave

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you have lost the join condition - you take the complete table a
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Following to the syntax of UPDATE statement,
    "update TABLE A ..." means that you want to update a table named "TABLE" and rename it A for referencing the table later in the statement.

    It would be better to show example(sample data of tables before and after updated) to describe what you want to do.

  8. #8
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    i assume the following code is what you are expecting

    update TABLE A
    set A.COL = 6794

    where A.id IN

    (select id from TABLE B
    where B.NAME = 'ABC'
    and date(B.STARTDATE) = date (getdate())
    )


    And if you are sure that the ID column is having unique values, then you can replace the operator 'IN' with '=' in my code, otherwise use the code as it is

Posting Permissions

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