From: David Johnston <polobo@yahoo.com>
To: 'Carlos Mennens' <carlos.mennens@gmail.com>
To: 'PostgreSQL \(SQL\)' <pgsql-sql@postgresql.org>
Subject: Re: Update Mass Data in Field?
Date: Thu, 26 Jan 2012 19:20:07 -0500
Message-ID: <003d01ccdc89$6ce132f0$46a398d0$@yahoo.com> (raw)
In-Reply-To: <CAAQLLO5fHwYyjtzDs98VAn6FwMW3dnp+uRYYX7-H11OA-WBqnw@mail.gmail.com>
References: <CAAQLLO5fHwYyjtzDs98VAn6FwMW3dnp+uRYYX7-H11OA-WBqnw@mail.gmail.com>
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Carlos Mennens
Sent: Thursday, January 26, 2012 6:59 PM
To: PostgreSQL (SQL)
Subject: [SQL] Update Mass Data in Field?
I'm new to SQL so I'm looking for a way to change several email addresses
with one command. For example everyone has a 'holyghost.org'
domain and I need to change a few 100 email addresses in the field
'emp_email'. I need to UPDATE employees table which has a COLUMN 'emp_email'
and change %holyghost.org to %ghostsoftware.com.
I tried:
UPDATE employees SET emp_email = '%ghostsoftware.com' WHERE emp_email LIKE
'%holyghost.org';
It didn't update anything when I ran the command. Does anyone know how I
need to correct my SQL statement above to change everyone's email address?
------------------------------------------------
Like Steve said, use the "regexp_replace" function. However, I am concerned
that nothing updated when you executed the above. What you should have seen
happen is that EVERY email address ending with "holyghost.org" became
changed to the literal value "%ghostsoftware.com" - which obviously is not
an e-mail address - and the original "holyghost.org" email address would
have been gone beyond easy recovery.
Takeaway: test update queries on sample data (or at least within a
transaction block) and confirm your results before executing against live
data (or committing the transaction).
David J.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, carlos.mennens@gmail.com
Subject: Re: Update Mass Data in Field?
In-Reply-To: <003d01ccdc89$6ce132f0$46a398d0$@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox