Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Red face Unanswered: Update query help needed

    I have oracle 9i on HP-UX server 10. I need a query to update a table with scenario as below:

    TableA in history database is similar to TableA in production database. I have schema like this:

    TableA has columns usercode char(20), passcode char(100), dateset datetime with data as below:

    usercode passcode dateset
    ABCD hmm01$1 08/20/2010 12:12:00
    ABCD xyz87@! 08/20/2010 11:12:00
    ABCD yyyyxx01 09/21/2010 13:20:01

    The unique key is on (usercode, dateset)

    There is a public database link from history to production (name=production)

    I have an issue where I need to update all records on tableA in history with those from production. I have the query like below and it gives error that query returns multiple records, so can not update. I do not know how to make this to work. The query I used to update is as below (I know it is wrong).

    update tableA a set passcode=(select passcode from tableA@production b where b.usercode=a.usercode and a.dateset=b.dateset) where a.usercode='TESTME'

    I have copied all records from live to history database for the table, so only update of data is needed to sync tables. Trying to see if I can successfully update for usercode='TESTME' before I attempt to do for all usercodes. I know the main update query returns multiple records, so it is unable to update. What is the right way to do this update? Any help is appreciated.

    This is just a small table I am trying to do it and there are many other tables like 50 of them with different unique indexes(PK) and if this is correct, I can write the queries for other tables also.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have copied all records from live to history database for the table,
    I do not understand.
    If the whole record got moved from live DB to history DB, why does anything else need to be done.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Wink Update query help

    I am sorry that did not say correctly. The situation was there was some missing data in between some dates in history and I copied that missing data from live to history. There are also some data in live that got changed after that missing data is filled. I need to copy those updated changes from live to history for which the data already exists in history. I know the date critiria to filter out but do not know how to write the right query to update statments. There are a few (multiple columns that I need to do updates) with table having composite column primary key. Any help to write such a query is highly appreciated.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Sorry to keep asking questions, but I am only trying to understand your environment.
    It appears that TableA gets a new record every time some user changes their passcode.
    If this is true, at least to my way of thinking, TableA does not get UPDATE; only INSERT.
    If table in HISTORY gets built from data in TableA, then I still do not see where any UPDATE is needed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Wink Update query help

    The situation is like this: There is a table in production (say TableX) that gets populated with records by triggers whenever TableA is DMLed. That means TableX gets a string with flag U for update and A for addition and D for update with keyvalues related to TableA. There is a batch process that runs and parses TableX and does actions depending upon DML to TableA in history. I have lost for a couple of days the records in TableX. I could copy all new ones to history TableA from production TableA. Now I need to do updates and deletes in sync from TableA in production to TableA in history. That is where I am stuck for update query. Any help is really appreciated. Hope that gives more picture of the issue.

  6. #6
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134
    No takers who can assist me on this?

  7. #7
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    What is wrong with your update statement?

    My table a:
    ABCD fffffff 10/8/2010
    ABCD fffffff 10/9/2010
    ABCD fffffff 10/10/2010

    My table b (which is production):
    ABCD 09fffffff 10/9/2010
    ABCD 08fffffff 10/8/2010
    ABCD 10fffffff 10/10/2010
    ABCD 11fffffff 10/11/2010

    Issued below command:
    update a
    set passcode=(select passcode from b
    where b.usercode=a.usercode and a.dateset=b.dateset)
    where a.usercode='ABCD'


    following is the result content in b:

    ABCD 08fffffff 10/8/2010
    ABCD 09fffffff 10/9/2010
    ABCD 10fffffff 10/10/2010

    Is that not your desired result, which is the latest values in production env?
    Thanks and Regards,

    Praveen Pulikunnu

Posting Permissions

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