Re: dummy update

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