Thread: using a correlated subquery in update

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*])');

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

Thanks,
Sean

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/

-----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-----

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-----

On Fri, Dec 03, 2004 at 02:46:16PM -0500, Andrew Hammond wrote:

> It sounds like he just wants to truncate the '.11' from the end of a string.

We don't know if all strings match that pattern exactly or if it
was just an example.  He might also need to strip '.5' and '.6789'.

> UPDATE foo
> SET xm_value = substring(xm_value FOR char_length(xm_value) - 3)
> WHERE xm_value LIKE '%.11';

That covers a specific case.  Here are some more general solutions
that might work (or might not, depending on the string format):

substring(xm_value FROM '(.+)\\.[0-9]+$')
rtrim(rtrim(xm_value, '0123456789'), '.')
split_part(xm_value, '.', 1)

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