Appending strings to columns
On 19 Aug 1994 nailerp@leeds.syntegra.bt.co.uk wrote:
> This bit of SQL doesn't work with Informix:
>
> update table1 set column1 = column1 || "a text string">
> Anybody know of a workaround?
>
> Paul Nailer
>
This is pretty ugly but it works.
1. You store both fields in a row of a temp table.
2. Unload the temp table to a flat file specifying a blank delimiter.
3. Create another temp table with a single column.
4. Reload the flat file record to the second temp table with no delimiter
specified.
5. Finally, use a subquery on the second temp table for the update.
Of course, the where clause needs to be identical in the select and the update.
The following assumes that your column1 does not exceed 255 characters.
It also puts a blank between the concatenated fields in the new column 1.
*** step 1
select column1, "new text" newt
from table1
where xxxxxxxxxxxxxxxxxxxxx
into temp hold_stuff
with no log;*** step 2
unload to "hold_file" delimiter " "
select test_string, newt from hold_stuff;*** step 3
create temp table hold_stuff2
(new_field char(255))
with no log;*** step 4
load from "hold_file"
insert into hold_stuff2;*** step 5
update lab_request
set test_string =
(select new_field from hold_stuff2)
where xxxxxxxxxxxxxxxxxxxxx;
drop table hold_stuff;
drop table hold_stuff2;
Good luck
--
Con Woodall Colorado St. U.; Veterinary Teach. Hosp.; Ft. Collins CO 80523;
303-491-1244 FAX 303-491-4414 cwoodall@vth1.vth.colostate.edu
You can do anything with a computer, but you might not want to.
--