pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate Mass Data in Field?
3+ messages / 3 participants
[nested] [flat]
* Update Mass Data in Field?
@ 2012-01-26 23:59 Carlos Mennens <carlos.mennens@gmail.com>
2012-01-27 00:06 ` Re: Update Mass Data in Field? Steve Crawford <scrawford@pinpointresearch.com>
2012-01-27 00:20 ` Re: Update Mass Data in Field? David Johnston <polobo@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: Carlos Mennens @ 2012-01-26 23:59 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update Mass Data in Field?
2012-01-26 23:59 Update Mass Data in Field? Carlos Mennens <carlos.mennens@gmail.com>
@ 2012-01-27 00:06 ` Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2012-01-27 00:06 UTC (permalink / raw)
To: Carlos Mennens <carlos.mennens@gmail.com>; +Cc: pgsql-sql
On 01/26/2012 03:59 PM, Carlos Mennens wrote:
> 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?
>
...set emp_email = regexp_replace(emp_email, '@holyghost.org$',
'@ghostingsoftware.org') where emp_email ~ '@holyghost.org$'...
This is using the regular expression match and regexp_replace to ensure
that the pattern is anchored at the end of the field and includes the
"@" sign in the expression to avoid accidentally matching something like
...@theholyghost.org.
You can always do a select of the emp_email alongside the replacement
expression to be sure it will do what you want before actually updating
your database.
Cheers,
Steve
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update Mass Data in Field?
2012-01-26 23:59 Update Mass Data in Field? Carlos Mennens <carlos.mennens@gmail.com>
@ 2012-01-27 00:20 ` David Johnston <polobo@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: David Johnston @ 2012-01-27 00:20 UTC (permalink / raw)
To: 'Carlos Mennens' <carlos.mennens@gmail.com>; pgsql-sql
-----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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-01-27 00:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-26 23:59 Update Mass Data in Field? Carlos Mennens <carlos.mennens@gmail.com>
2012-01-27 00:06 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-01-27 00:20 ` David Johnston <polobo@yahoo.com>
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