Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    4

    Unanswered: multiple row update

    I want to update multiple rows of data in a table and was given all the data in an excel spreasheet. The data looks like:

    UPDATE ITEMLOC SET user_field1 = '66218009' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100426'
    UPDATE ITEMLOC SET user_field1 = '66218066' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100434'
    UPDATE ITEMLOC SET user_field1 = '66216607' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100532'
    UPDATE ITEMLOC SET user_field1 = '66216607' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100533'
    UPDATE ITEMLOC SET user_field1 = '66218025' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100633'
    UPDATE ITEMLOC SET user_field1 = '66217241' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100640'
    UPDATE ITEMLOC SET user_field1 = '66217241' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100641'
    UPDATE ITEMLOC SET user_field1 = '66218025' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100643'
    UPDATE ITEMLOC SET user_field1 = '66218009' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100654'
    UPDATE ITEMLOC SET user_field1 = '66242124' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100672'
    UPDATE ITEMLOC SET user_field1 = '66242124' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100673'
    UPDATE ITEMLOC SET user_field1 = '66239716' where itemloc.company = '1' and itemloc.location = '01661' and itemloc.item = '100698'

    I know I can only run one "UPDATE" at a time and was wondering how to format a sql statement to process these 10,000 lines. Can this be done?

    Any help is appreciated.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Export the data to a csv file and either load to ttemp table with sql loader or create an external table, then do:
    Code:
    UPDATE ITEMLOC i
          SET user_field1 = (
           SELECT user_field1 FROM tmpTable t
            WHERE t.company = i.company and t.location = i.location
                and t.item = i.item)
    WHERE EXISTS (
    SELECT 1 FROM tmpTable t
     WHERE t.company = i.company and t.location = i.location
         and t.item = i.item);

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2006
    Posts
    4

    Thumbs up

    Thank You!! That worked perfectly.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Glad it worked for you!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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