RE: Multiple UPDATE statements in 1 file. How Do I do it?
Regardless of the approach which you utilize, I do not believe that
you can use a substring in the SET part of the UPDATE command.
You must set the entire column to a new value, not just the first
2 characters of the column. For example,
UPDATE gc_main
SET area="W110"
WHERE area[3,4]="10" ;
UPDATE gc_main
SET area="W223"
WHERE area[3,4]="23" ;
Rick
-----Original Message-----
From: Andrew Hamm [mailto:ahamm@sanderson.net.au]
Sent: Tuesday, February 06, 2001 16:22
To: informix-list@iiug.org
Subject: Re: Multiple UPDATE statements in 1 file. How Do I do it?
John wrote in message <3a8083d6.194645047@netnews.voicenet.com>...
>I need to make a single file with multiple updates in it.
>The following update needs to be run a total of 72 times and in each
>instance the SET AREA and WHERE AREA parameters will be different.
>
>UPDATE gc_main
>SET area[1,2]="W1"
>WHERE area[3,4]="10"
>
>UPDATE gc_main
>SET area[1,2]="W2"
>WHERE area[3,4]="23"
>
>What is the syntax for running multiple updates?
Emmm Errr - I think I understand your question.
You want to use an .sql file with dbaccess or isql, correct?
You want to loop over a set of data.
Here's how I would do it using a ksh script instead of an .sql script (which
can't loop):
while read area12 area34; do
echo "update gc_main set area[1,2] = '$area12' where area[3,4] =
'$area34'"
done <<++ | dbaccess mydatabase -
W1 10
W2 23
W3 45
etc etc
++
ksh scripting is very powerful and a whole lot of fun. If it doesn't make
any sense, just use it like a recipe. Have Fun!