SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2002
    Posts
    161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Assistance with SQL Query on Informix DB

    Hi Guys

    I have searched the net, read a book and I still can't seem to get the syntax correct for the following problem.

    I have been sent 2 columns of data that contain 45,000 rows within an Excel spreadsheet to update on an informix database, although not every PK record needs the usa_flg updated. The columns to be updated to include the following via Query Tool ODBC:

    fir_num (PK - int), usa_flg (char) - check out the screenshot to see.

    I've tried all sorts of INSERT and UPDATE queries to no avail. I think it's the volume of records that's stopping me.

    Does anyone have any experience in updating thousands of records based on the PK?

    The closest I've been able to produce is:

    UPDATE table
    WHERE fir_num, usa_flg
    SET VALUES (1, 2342342
    2, 9230490
    3, 4329043)
    ...
    45000,023409)
    Attached Images Attached Images

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by RichardN View Post
    although not every PK record needs the usa_flg updated.
    how can you tell which ones do?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2002
    Posts
    161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it's in the spreadsheet then it needs to be updated otherwise the rows not in the spreadsheet can remain as they currently exist in the database.

    Cheers

    Richard

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, i have zero experience with informix, but i think it's safe to say that you should load up the excel file into a table, and then do a joined update

    informix has a multi-table version of the UPDATE statement, so it would go something like this...
    Code:
    UPDATE mytable
       SET mytable.usa_flg
      FROM mytable
         , exceltable
     WHERE mytable.fir_num = exceltable.fir_num
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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