Update bad addresses

[Chris]

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.

[Steve]

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" <[e-mail]:

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.

[Andrew J. Kelly]

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.

[Chris]

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" <[e-mail]:

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" <[e-mail]:

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.

[Chris]

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" <[e-mail]:

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" <[e-mail]:

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.

[Ed Murphy]

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)