Need advice on best way to make dbase record updates

[yvan]

Approximately once a month, a client of ours sends us a bunch of
comma-delimited text files which I have to clean up and then import
into their MS SQL database. All last week, I was using a Cold Fusion
script to upload the cleaned up files and then import the records they
contained into the database, though obviously, the process took
friggin' forever, and could have been done 500x quicker had I done it
directly on the server. My SQL knowledge is somewhat limited, however,
so I had no choice but to stick to what I know, which is Cold Fusion
programming.

In the process of cleaning up some of these comma-delimited text files,
I inadvertently messed up some of the 10-digit zip codes, by applying
the wrong Excel formula to the ZIP columns. These records were imported
into the database with obviously incorrect zip codes (ie: single
digit). So now, I have to find the best and quickest way possible to
compare these records in the database (that have the single digit zip
codes) with the unmodified data, and to update the zip codes with the
correct data.

I've had no luck setting up a TEXT file as an ODBC datasource, -- so
I've ruled that out completely. I've also managed to import the
unmodified data into an Access database, and to set it up as a Cold
Fusion datasource. But it seems this 2nd road I've been traveling down
is not the ideal approach either.

My question is, -- assuming that I'll be able to import the records
from the Access database into their own table on the SQL server, -- how
should I go about the process of updating these records that have the
incorrect zip codes?

Here is the specific logic I would need to employ:

* Here is a list of records, each of which contains an incorrect
1-digit zip code (Database A / Table A)

* Here is a much longer list of records (which contains all of the
records from Database A / Table A + thousands more), each of which
contains a correct 5-digit zip code (Database B / Table B)

* Compare both lists of records and run the following query/update:

When a record in Database A / Table A has matching "name", "address1",
and "address2" values as a record in Database B / Table B -- update the
record in Database B / Table B with the zip code from the matching
record in Database A / Table A.

Would anyone care to write a sample query for me that I could run
directly on the SQL server, or at least give me some pointers?

The specific field names are as follows:
name,address1,address2,city,state,zip

Thanks in advance!
- yvan

[Sri]

Give this a shot:
Here is a list of records, each of which contains an incorrect
1-digit zip code (Database A / Table A)

select name, address1, address2, zipcode
from dbA.tblA where len(zipcode) = 1 -- this will retrieve all the zip
codes records of len = 1
select count(*) from dbA.tblA where len(zipcode) = 1 -- this will give
the counts

* Here is a much longer list of records (which contains all of the
records from Database A / Table A + thousands more), each of which
contains a correct 5-digit zip code (Database B / Table B)

select A.zipcodeA, A.record, B.Record
from dbA.tblA A
INNER JOIN dbB.tblB ON
A.record = B.Record

IF you have more record of tblA in tblB then a cross join will work but
I am not clear what is your specification.
* Compare both lists of records and run the following query/update:
When a record in Database A / Table A has matching "name", "address1",
and "address2" values as a record in Database B / Table B -- update the

record in Database B / Table B with the zip code from the matching
record in Database A / Table A.

UPDATE dbB.tblB
SET zipcode = A.Zipcode
from dbB.tblB B
INNER JOIN dbA.tblA A ON
A.name = B.name and
A.address1 = B.address1 and
A.address2 = B.address2

If you will have more question let me know in my gmail account that is
sp**************@gmail.com

thanks
sri

Would anyone care to write a sample query for me that I could run
directly on the SQL server, or at least give me some pointers?
The specific field names are as follows:
name,address1,address2,city,state,zip

[Sri]

my email id is before the _no_s part and gmail.com

thanks
Sri

[Andy O'Neill]

<yv**@ideasdesign.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
<<>>

My question is, -- assuming that I'll be able to import the records
from the Access database into their own table on the SQL server, -- how
should I go about the process of updating these records that have the
incorrect zip codes?
If you are not confident with SQL then I'd suggest doing it from access.
This could be slower, but you can check what'll happen easier...

Once your sql dsn is set up, attach the table in sql.
Select new query.
Choose one of your tables.
From the query drop down menu, select update.
Add in your other table.
Create the relationships between the two tables by clicking on name in one
and dragging to name in the other.
A line will appear.
Repeat for rest.
Add your zip code and put something like [tablename].[zip] in the update
cell.
IF it matters that you exclude the ones no wrong you can detect this using
len([tablename].[zip])
as an additional column and criteria > 1

Up the top of the query window you have a drop down combo with set square
and such ( indicating design mode ).
If you choose the table thing off there then it'll show you what it'll
update.
You could temporarily add fields by double clicking.
That way you can be confident.

As I think you will see, this approach enables you to write stuff with
pretty much zero access or SQL knowledge.

Putting something together in access added future loads in with less work
seems quite possible.
Certainly, you could verify your data before loading it.

Of course if the client was to send you an access table with their data in
each time then you could create this and make the table ensure some data
integrity.
Maybe that's not an option though.
When a record in Database A / Table A has matching "name", "address1",
and "address2" values as a record in Database B / Table B -- update the
record in Database B / Table B with the zip code from the matching
record in Database A / Table A.

Would anyone care to write a sample query for me that I could run
directly on the SQL server, or at least give me some pointers?

The specific field names are as follows:
name,address1,address2,city,state,zip

Thanks in advance!
- yvan

[yvan]

Okay, .. I've gotten a bit further on the creation of my SQL update
statement. However, I am having difficulty assigning the table name
aliases correctly.

This is what I have so far (A = messed up zips / B = correct 5-digit
zips):
UPDATE mydbase.dbo.mytable1 A
SET A.zip = LEFT(B.zip,5)
INNER JOIN mydbase.dbo.mytable2 B
ON A.name = B.name
AND A.address1 = B.address1
AND A.address2 = B.address2
WHERE A.entered = '1/21/2005'
AND A.magID = 1
AND A.scid = 0
AND (A.zip = '00000'
OR A.zip = '00001'
OR A.zip = '00002'
OR A.zip = '00003'
OR A.zip = '00004'
OR A.zip = '00005'
OR A.zip = '00006'
OR A.zip = '00007'
OR A.zip = '00008'
OR A.zip = '00009')
When I attempt to run this update statement AS IS, I get the following
error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'A'.
Anyone know what the proper way to assign table name aliases is in this
scenario?

Thanks,
- yvan

[Erland Sommarskog]

(yv**@ideasdesign.com) writes:

UPDATE mydbase.dbo.mytable1 A
SET A.zip = LEFT(B.zip,5)
INNER JOIN mydbase.dbo.mytable2 B
ON A.name = B.name
AND A.address1 = B.address1
AND A.address2 = B.address2
WHERE A.entered = '1/21/2005'
AND A.magID = 1
AND A.scid = 0
AND (A.zip = '00000'
OR A.zip = '00001'
OR A.zip = '00002'
OR A.zip = '00003'
OR A.zip = '00004'
OR A.zip = '00005'
OR A.zip = '00006'
OR A.zip = '00007'
OR A.zip = '00008'
OR A.zip = '00009')
When I attempt to run this update statement AS IS, I get the following
error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'A'.
Anyone know what the proper way to assign table name aliases is in this
scenario?

Use a FROM clause. That's not a standard SQL thing, but a very very
handy proprietart extension to SQL.

The syntax for UPDATE is Books Online. While it may look bewildering,
it's probably more effective to read it that post questions about
syntax and wait for answers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp