Re: using a correlated subquery in update

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