Assistance with SQL Query on Informix DB

[RichardN]

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)

[r937]

although not every PK record needs the usa_flg updated.

how can you tell which ones do?

[RichardN]

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

[r937]

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...

UPDATE mytable
   SET mytable.usa_flg
  FROM mytable
     , exceltable
 WHERE mytable.fir_num = exceltable.fir_num