Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Alternatives to CURSORs

    Hi all

    I have often come across discussions on this forum saying that CURSORs are expensive in time (processing power?).

    Having used CURSORs to processing a mere 2000+ record (not much at all) which took a fair while to complete, I now realize why you guys are saying CURSORs are expensive.

    But is there alternatives to using CURSORs in the situation where I try to process every records returned by a particular query?

    Say for example, i want to update columns that comes from different tables for every record that is returned by a SELECT JOIN query. there is no way that i can do that with a single UPDATE statement cause i can't do JOIN with UPDATE query.


    All comments welcome


    James

  2. #2
    Join Date
    Dec 2003
    Posts
    31
    well u can :
    update a set aa= b.bb
    from table_a a
    inner join table_b b on a.key = b.key

    since in cursors u use loops try :
    loop on numeric key in table

    select @i = Min(int_key) From Tablename
    while @i <= (select @Max(int_key) From Tablename)
    begin
    .
    .

    end

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    yes you can join tables in the from clause of an update statement
    [BOL] UPDATE (described)

    check out example 'C' at the bottom of the help document

  4. #4
    Join Date
    Aug 2003
    Posts
    111
    Thank you guys.

    Is it standard ANSI to use join in an update query? Although it would make sense that it is. I have tried it before without success for some reason. I will try it again.

    James

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, JOIN operations in an UPDATE are explicitly forbidden by the ISO, and were never addressed by ANSI. While JOIN operations in an UPDATE can be convenient, they violate most of the rules of relational algebra. Sybase and Microsoft are the only commercially successful engines I can think of that support them.

    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Really?

    So in Oracle, for instance, you can't execute a statement like:

    update A
    set A.Column = NewValue
    from A
    inner join B on A.Key = B.Value

    ???

    ANSI or not, that's pretty simple and pretty convenient too.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    No, JOIN operations in an UPDATE are explicitly forbidden by the ISO, and were never addressed by ANSI. While JOIN operations in an UPDATE can be convenient, they violate most of the rules of relational algebra. Sybase and Microsoft are the only commercially successful engines I can think of that support them.

    -PatP
    Nope...even DB2 OS/390 can do it now...it's just extremely painful...

    But we did have a very good thread where we discussed how I "crossed the line" and broke the rules...

    I gotta look it up...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    subqueries are useful here, as they provide for the referencing of tables.
    normally in a complex update or delete i will create a query that doesnt change the data and after i recieve the correct results, i will use it as a subquery for the update\delete stmt. especially if the sarg is a dynamic value.

    update t1
    set c2 = x
    where col3 in (select col3 from t2
    where col4 = x)

    however, be carefull with subqueries as they can have some definite disadvantages. specifically correlated subqueries

    in addition when you join tables in an update\delete, the sql optimizer has a great deal of flexibility with the join operations where in the subquery the inner and outer queries kind of restrict the optimizers options.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by blindman
    Really?

    So in Oracle, for instance, you can't execute a statement like:

    update A
    set A.Column = NewValue
    from A
    inner join B on A.Key = B.Value

    ???

    ANSI or not, that's pretty simple and pretty convenient too.
    I rarely think of Oracle, or at least I try not to.

    I didn't realize that DB2 supported this form of blaspheme. I'm sure that it is great fun listening to Celko on this topic!

    -PatP

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's just so damn useful....in the (DB2) old days when it didn't, you had to either use a cursor, or genrate the satements and then execute them in a batch...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As far back as I can remember, DB2 supported sub-queries. Sub-queries are safe to use in an UPDATE as long as they are stochastic and deterministic. I don't know when DB2 added support for JOIN operations within an UPDATE.

    -PatP

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think it was back in V5...

    and whoah...

    had to look that one up

    http://www.hyperdictionary.com/dictionary/stochastic
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ooops, my bad. I meant non-stochastic. Sorry.

    -PatP

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thought it was kind of like oil and water....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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