Re: using a correlated subquery in update

On Fri, Dec 03, 2004 at 08:18:27AM -0500, Sean Davis wrote:

> I have a table that has a column that has values like XM_29832.11 and I
> want to do an update to take off the .11 part.  I can do this for a
> single value using:
>
> select substring('XM_29832.11' from '^([A-Z]*_[0-9*])');

This query returns 'XM_2' -- is that what you want?  Your description
sounds like you'd want 'XM_29832'.  Or is the query wrong because
you typed it into the message instead of cutting and pasting it?

> However, how can I write an update to use the above as a subquery to
> update the whole column at once?

UPDATE foo SET value = substring(value FROM '^([A-Z]*_[0-9]*)');

I'd suggest making the update in a transaction so you can verify
that the changes are correct before committing them.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/