isql update records through text file 1

[HebieBug]

I would like to run a query from isql that will update records from a text file.
Lets say I have a table called table1 with the fields
id --> incremental primary key
Name --> varchar(30)

I then have a test file in the format
10|something|
22|somthing2|
44|lala|


Through isql the first cell from the text file matches it to the table and then updates the Name field with the text files second field.

I can change the text file to any format needed.
Does anyone know if this is possible or how to get a recordset to be updated from a text file ???

 

[olded]

Hi:

A way is to read each record in the text file and perform an update:

#!/bin/ksh

DBNAME=testdb
INTERFACE_CMD="dbaccess"; export INTERFACE_CMD
DBCOMMAND="$INTERFACE_CMD -e $DBNAME"; export DBCOMMAND

while IFS="|" read col1 col2
do # read assumes there's two columns
echo $col1
$DBCOMMAND <<MSG
UPDATE table1 SET name = &quot;$col2&quot; WHERE id = &quot;$col1&quot;
MSG
done < data.file

Regards,

Ed

 

[olded]

Hi:

and this version does it all in dbaccess/isql:

#!/bin/ksh

DBNAME=testdb
INTERFACE_CMD=&quot;dbaccess&quot;; export INTERFACE_CMD
DBCOMMAND=&quot;$INTERFACE_CMD -e $DBNAME&quot;; export DBCOMMAND

$DBCOMMAND <<MSG
CREATE TEMP TABLE tb (
id INTEGER,
name VARCHAR(30)
) WITH NO LOG;
LOAD FROM data.file INSERT INTO tb;
UPDATE table1 SET (name) = ((SELECT name FROM tb WHERE tb.id = table1.id))
WHERE id IN (SELECT id FROM tb);

MSG

 

[HebieBug]

Cool dude.
That's some fasinating stuff.
Every time I look at informix it seems to grow in power and versitility.
That code is mind blowing.
You have saved me about 4hours worth of work per week especially as I work the night owl shift.
Hebiebug might even get 7 hours sleep one of these days.