Re: Update Mass Data in Field?

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: "'Carlos Mennens'" <carlos(dot)mennens(at)gmail(dot)com>, "'PostgreSQL \(SQL\)'" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Update Mass Data in Field?
Date: 2012-01-27 00:20:07
Message-ID: 003d01ccdc89$6ce132f0$46a398d0$@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)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.

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Tim Landscheidt 2012-01-27 02:13:52 Re: Getting a list of a table's attributes that are sortable sorted by uniqueness
Previous Message Steve Crawford 2012-01-27 00:06:43 Re: Update Mass Data in Field?