Results 1 to 8 of 8

Thread: Updation

  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: Updation

    Hi

    I want to update a table
    the table structure is
    app_id
    seq_num
    value
    the app_id and seq_num form the primary key
    For a single app_id there will be upto max 10 seq num
    so the data in the table will be
    app-id seq num value
    123 1 Other
    123 2 Doc
    123 3 Page

    I want to update all the records for the app_id 123
    Update abc
    set value = 'Document'
    where app_id = 123 and seq_num = 1

    this way i will have to write upto max 10 update statements in a Stored procedure
    Is there any way that in only one update statement i can update the values in the table for the app id 123

    i will be creating stored procedure to update the table which will have the app_id and the values as the input parameters
    i.e 123,Others,Doc,Page
    the values passed will be in the order of the seq num
    ie Other for seq num 1
    Doc for seq num 2
    as so on...
    So can update the values in one update statement.

    Need help

    Regards
    Shalu
    Regards
    Shalu

  2. #2
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    Re: Updation

    Originally posted by shalu_91
    Hi

    I want to update a table
    the table structure is
    app_id
    seq_num
    value
    the app_id and seq_num form the primary key
    For a single app_id there will be upto max 10 seq num
    so the data in the table will be
    app-id seq num value
    123 1 Other
    123 2 Doc
    123 3 Page

    I want to update all the records for the app_id 123
    Update abc
    set value = 'Document'
    where app_id = 123 and seq_num = 1

    this way i will have to write upto max 10 update statements in a Stored procedure
    Is there any way that in only one update statement i can update the values in the table for the app id 123

    i will be creating stored procedure to update the table which will have the app_id and the values as the input parameters
    i.e 123,Others,Doc,Page
    the values passed will be in the order of the seq num
    ie Other for seq num 1
    Doc for seq num 2
    as so on...
    So can update the values in one update statement.

    Need help

    Regards
    Shalu
    Hi,

    let's check If I understood you correctly:

    You have a tab "A" where tha data looks like:

    app-id seq num value
    -------------------------------
    123 1 Other
    123 2 Doc
    123 3 Page

    for each app-id yopu wan to update up to 10 seq_nums with some value. Is this value always the same for all "app-id seq num" combinations? Example:

    app-id seq num value
    -------------------------------
    123 1 Other002
    123 2 Doc002
    123 3 Page002
    124 1 Other002
    124 2 Doc002
    124 3 Page002

    If it's always the same value then we can try it with sql but basically I would do a loop in your application calling the stored procedure 10 times passing each time a different value.


    regards,

    sl
    kdb is the solution of all problems.
    Just try it, www.kx.com

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    calling the stored procuedure 10 times means hitting the database 10 times, i want to hit the database only once from the calling application
    Regards
    Shalu

  4. #4
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    ?

    Hi,

    SQL does not do bulk updates, you will hit the db 10 times anyway, even more sometimes. So there is no point of being afraid calling a SPROC several times.

    SL
    kdb is the solution of all problems.
    Just try it, www.kx.com

  5. #5
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    On the other hand

    You can develop a more sophisticated stored procedure having a temporary table a loop and whatever inside. But is it worth the pain?

    SL
    kdb is the solution of all problems.
    Just try it, www.kx.com

  6. #6
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    Angry !!!

    No way of doing it in one SQL stmt.
    kdb is the solution of all problems.
    Just try it, www.kx.com

  7. #7
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21

    Re: Updation

    I'm not sure I totally understand. But does this come close?

    Update abc
    set value = case seq_num
    when 1 then "First Value"
    when 2 then "Second Value"
    when 3 then "Third Value"
    ...
    when 10 then "Tenth Value"
    else NULL
    end
    where app_id = 123

  8. #8
    Join Date
    Jul 2003
    Posts
    16
    Thanks for the information
    Regards
    Shalu

Posting Permissions

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