DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=R7WZZcH6hJg8YPWbJVtDk4lhGrFurjNhqVJ2VrDEzQHmVH+h5ssO3jsY7G/7I650P7IEjerCB4O7R1Y4tDJ8yVFCCoUg46PL6ZOgN/8lkWf4FbGeIZJB+zZXp2ueEPeBWKFHckoG3v51chhrcdY6OqJvblR3zrFrDzxc5vMtOYo=
	;
From: "David Johnston" <polobo@yahoo.com>
To: "'Carlos Mennens'" <carlos.mennens@gmail.com>,
	"'PostgreSQL \(SQL\)'" <pgsql-sql@postgresql.org>
References: 
 <CAAQLLO5fHwYyjtzDs98VAn6FwMW3dnp+uRYYX7-H11OA-WBqnw@mail.gmail.com>
In-Reply-To: 
 <CAAQLLO5fHwYyjtzDs98VAn6FwMW3dnp+uRYYX7-H11OA-WBqnw@mail.gmail.com>
Subject: Re: Update Mass Data in Field?
Date: Thu, 26 Jan 2012 19:20:07 -0500
Message-ID: <003d01ccdc89$6ce132f0$46a398d0$@yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQGeA09Cd9JaQl9cWgo32aqUbbx7N5Z9CuNw
Content-Language: en-us

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