Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009

    Unanswered: update data using select

    Hi I am trying to update some data in a stored procedure and do now want to use a cursor/loop. I am trying to do the below

    The select query below returns me the 2nd row on the resultset for the id 3329

    (SELECT ID,ROWNUM RN,value FROM table1
    WHERE ID = 3329
    AND TYPE = 'CodeTyp')
    WHERE RN = 2

    However I want to use this on an update with multiple ids as below

    UPDATE table2
    comm_code = ( SELECT value FROM
    (SELECT ID,ROWNUM RN, value FROM table1
    WHERE ID =
    AND TYPE = 'CodeTyp')
    WHERE RN = 2 ) ;

    However I keep getting is an invalid identifier any ideas how to get this done?

  2. #2
    Join Date
    Dec 2008
    this the link where you can learn how can update data using stored procedure...
    Using Stored Procedures to Update Data

  3. #3
    Join Date
    Jan 2009
    Dhaka, Bangladesh
    try this :

    UPDATE table2 y
    y.comm_code = ( SELECT x.value FROM
    FROM table1x
    WHERE = and x.type = 'CodeTyp' and rownum = 2 )
    Last edited by hasan_uiu; 02-04-09 at 03:32.
    Mohammad Hasan Shaharear

  4. #4
    Join Date
    Mar 2007
    @hasan_uiu: The condition ROWNUM=2 will never return a row. Have a look at this thread on AskTom for explanation:

    @qts1: Are you aware, that the order of rows in the resultset may change with every execution of the query (is not deterministic), as you do not use ORDER BY clause?
    Anyway, if it really ends with this error, you may use ROW_NUMBER analytic function partitioning by ID and putting the condition into the outer query. It may however return different rows than your query; but according to its undeterminism I would not mind it. It may also be not very performant for huge tables.
    If you would post a testcase, I would put here SQL; but I am not willing to post here code I never managed to make.

    [Edit: typo]

  5. #5
    Join Date
    Feb 2009
    Rownum = 2 does not return any rows .. I guess I need to select a particular rownumber before I can use it.. I need to basically transpose these columns however I do not have a distinguishing attribute among the rows ... the rows are like below

    Id Seq num Type Value
    111 2 CodeTyp 1245
    111 4 CodeTyp 45869
    111 6 TranTyp XXX
    111 8 CodeTyp 96966

    I basically want to put this in a table as below

    ID codetyp1 codetyp2 codetyp3
    111 1245 45869 96966

    My sequence numbers can change so I have no other indicator to differentiate among the rows

  6. #6
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.

    use <code_tags> is explained in #1 STICKY post (URL below)
    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.

  7. #7
    Join Date
    Mar 2007
    Quote Originally Posted by qts1
    My sequence numbers can change so I have no other indicator to differentiate among the rows
    What about the VALUE itself? It would not matter even in case of duplicates.

    This might be a start:
    SELECT id, value, ROW_NUMBER() OVER (PARTITION BY id ORDER BY value) rn
    FROM table1
    WHERE type = 'CodeTyp';
    Then, it is (nearly) classical pivot query - just UPDATE instead INSERT. It is not necessary to UPDATE column by column, you may UPDATE them all at once. But I doubt data consistency (in comparition with DELETE-INSERT) though - if there is no row with given ID in TABLE2, nothing would be propagated. As you UPDATE all rows in TABLE2, INSERT should not be slower. Just take it a suggestion - the problem might be more complex than you posted (or than I understood).

Posting Permissions

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