By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,203 Members | 1,620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,203 IT Pros & Developers. It's quick & easy.

Update bad addresses

P: n/a
I got got a pile of bad email addresses to update in our SQL database.
I know how to do this for individual records using the update command.
Is there a way to execute an update using the list of addresses in an
excel spreadsheet or some other form of list? This kind of scripting
is new territory for me.

Jan 27 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
If your Excel file has a column matching the the Primary Key of the
database table, in addition to the email address column, you can use
DTS(2000) or SSIS(2005) to import it into a table and use an INNER JOIN
in you UPDATE.

On Jan 26, 4:23 pm, "Chris" <cjscu...@gmail.comwrote:
I got got a pile of bad email addresses to update in our SQL database.
I know how to do this for individual records using the update command.
Is there a way to execute an update using the list of addresses in an
excel spreadsheet or some other form of list? This kind of scripting
is new territory for me.
Jan 27 '07 #2

P: n/a
Your best bet is to import the addresses using DTS or SSIS and then doing an
update join.

--
Andrew J. Kelly SQL MVP

"Chris" <cj******@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...
>I got got a pile of bad email addresses to update in our SQL database.
I know how to do this for individual records using the update command.
Is there a way to execute an update using the list of addresses in an
excel spreadsheet or some other form of list? This kind of scripting
is new territory for me.

Jan 27 '07 #3

P: n/a
Unfortunately I just have the email addresses.

I know I can get the primary key for an individual record using a
select statement like the one below.
use maindb
go
select *
from dbo.tblLead
where email = 'b**@address.com'

Is there a way to use the excel list in the place of the single bad
address and then dump the results into a new table? Then do the
update as described below as a separate process? If I wanted to just
delete bad addresses where I don't have a new address and leave the
rest of the lead information alone could I do that using an Inner join
in my update? All references I have found to the delete statement
talk about deleting entire rows of data and I don't want to do that.

Chris

On Jan 26, 6:32 pm, "Steve" <morrisz...@hotmail.comwrote:
If your Excel file has a column matching the the Primary Key of the
database table, in addition to the email address column, you can use
DTS(2000) or SSIS(2005) to import it into a table and use an INNER JOIN
in you UPDATE.

On Jan 26, 4:23 pm, "Chris" <cjscu...@gmail.comwrote:
I got got a pile of bad email addresses to update in our SQL database.
I know how to do this for individual records using the update command.
Is there a way to execute an update using the list of addresses in an
excel spreadsheet or some other form of list? This kind of scripting
is new territory for me.
Jan 29 '07 #4

P: n/a
Unfortunately I only have a list of addresses. I know I can retrieve
the primary key associated with an individual address using the
following select statement or somehting similar.

use leaddb
go
select primary_key, email
from dbo.tblLead
where email = 'b*******@address.com'

Is it possible to use a variable in the where statement that picks up
the data in the excel file and then dumps the results into a table for
the purposes of doing the update as described below? Also if I don't
have a new address to update with how do I delete the email address
but leave the rest of the record in tact? All of the references I
have seen to the DELETE statement refer to deleting rows from the
table. I don't want to do that - only the email address.

On Jan 26, 6:32 pm, "Steve" <morrisz...@hotmail.comwrote:
If your Excel file has a column matching the the Primary Key of the
database table, in addition to the email address column, you can use
DTS(2000) or SSIS(2005) to import it into a table and use an INNER JOIN
in you UPDATE.

On Jan 26, 4:23 pm, "Chris" <cjscu...@gmail.comwrote:
I got got a pile of bad email addresses to update in our SQL database.
I know how to do this for individual records using the update command.
Is there a way to execute an update using the list of addresses in an
excel spreadsheet or some other form of list? This kind of scripting
is new territory for me.
Jan 29 '07 #5

P: n/a
Chris wrote:
Unfortunately I just have the email addresses.

I know I can get the primary key for an individual record using a
select statement like the one below.
use maindb
go
select *
from dbo.tblLead
where email = 'b**@address.com'

Is there a way to use the excel list in the place of the single bad
address and then dump the results into a new table? Then do the
update as described below as a separate process? If I wanted to just
delete bad addresses where I don't have a new address and leave the
rest of the lead information alone could I do that using an Inner join
in my update? All references I have found to the delete statement
talk about deleting entire rows of data and I don't want to do that.
Say you create a table (tblBadAddresses) with one column (email) and
dump the Excel data into it, then the next step is as follows:

update tblLead
set email = null
where email in (select email from tblBadAddresses)
Jan 30 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.