Re: using a correlated subquery in update

Thanks for the replies.  As Michael pointed out, I had the * on the
wrong side of the bracket (typo).  Then, I did exactly what was
suggested and got the result I wanted.

Sean

On Dec 3, 2004, at 2:46 PM, Andrew Hammond wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Michael Fuhr wrote:
> | 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.
>
> It sounds like he just wants to truncate the '.11' from the end of a
> string.
>
> BEGIN;
>
> UPDATE foo
> SET xm_value = substring(xm_value FOR char_length(xm_value) - 3)
> WHERE xm_value LIKE '%.11';
>
> Then do some SELECTs to confirm that you've got what you need and if
> so,
>
> COMMIT;
>
> - --
> Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
> Database Administrator, Afilias Canada Corp.
> CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.5 (GNU/Linux)
>
> iD8DBQFBsMKIgfzn5SevSpoRAheBAJ4skcRJwPaWsi2Mm+YilzwGt4CNdwCeJdAq
> jNWkO2bOd7fTTb/FAo8ikFs=
> =1XDe
> -----END PGP SIGNATURE-----