On 15 Nov 2005 12:52:31 -0800, tomcaml@yahoo.com wrote
> hello!
>
> anyone have a script or trick to spit put dummy updates needed for
> all tables in all DB's?
>
> as in, update carriertbl set add_user = add_user where 1=1;
>
> thanks!
>
> tom

Execute the following script in dbaccess against a database of your choice:

unload to "executeme.sql"
select "update "||x1.tabname||" set "||x2.colname||"="||x2.colname
||" where 1=1;--"
from systables x1, syscolumns x2
where x1.tabid = x2.tabid and x1.tabid>=100
and x2.colno = 1;

Then run the resulting script against the same database. Then lather, rinse,
repeat as needed for other databases.

Hope this helps,

Carsten.
sending to informix-list

> unload to "executeme.sql"
> select "update "||x1.tabname||" set "||x2.colname||"="||x2.colname
> ||" where 1=1;--"
> from systables x1, syscolumns x2
> where x1.tabid = x2.tabid and x1.tabid>=100
> and x2.colno = 1;

Good script, but 1-st column is serial type often and you will find
message
" 232: A SERIAL column (....) may not be updated"
I modified this sql a few
------------
unload to "_execute_me.sql"
select "update "||x1.tabname||" set "||x2.colname||"="||x2.colname||"
where
1=1;--"
from systables x1, syscolumns x2
where x1.tabid = x2.tabid
and x1.tabid>=100
-- A SERIAL column may not be updated
and (x2.colno = 1 and mod(x2.coltype, 256) not in (6,18))
union
select "update "||x1.tabname||" set "||x2.colname||"="||x2.colname||"
where
1=1;--"
from systables x1, syscolumns x2
where x1.tabid = x2.tabid
and x2.tabid>=100
-- A SERIAL column may not be updated
and x2.colno = 2 and mod(x2.coltype, 256) not in (6,18)
and x2.tabid in (select x4.tabid from syscolumns x4
where x4.colno = 1 and mod(x4.coltype,
256) in
(6,18)
);
-----------------
--
Best regards
Vasyl