WHERE question

[ceconix]

Hello all, I am still learning about SQL, and have a question. I have
two tables. I want to update table 2 with data from table 1. Table 1
has two columns. ID and NUM. In Table 2, I have an ID column and a
bunch more columns plus another NUM column. Table 1 was pulled in
from excel. I need to update the NUM from Table 1 to NUM in table 2,
but the formating on the ID fields are different, although the same
numbers. Example: Table 1 ID is ##############, and Table 2 ID is
##___#____##___##___###__####_ where _ = NULL. Also, Table 2 is
formatted as CHAR(30).

Before I screw up the tables, will "WHERE table1.ID = table2.ID" work,
or do I need to do something else to get it to work?

Thanks!

[Erland Sommarskog]

ceconix (ce*****@gmail.com) writes:

Hello all, I am still learning about SQL, and have a question. I have
two tables. I want to update table 2 with data from table 1. Table 1
has two columns. ID and NUM. In Table 2, I have an ID column and a
bunch more columns plus another NUM column. Table 1 was pulled in
from excel. I need to update the NUM from Table 1 to NUM in table 2,
but the formating on the ID fields are different, although the same
numbers. Example: Table 1 ID is ##############, and Table 2 ID is
##___#____##___##___###__####_ where _ = NULL. Also, Table 2 is
formatted as CHAR(30).

Before I screw up the tables, will "WHERE table1.ID = table2.ID" work,
or do I need to do something else to get it to work?

I don't really know what you mean with _ = NULL, but I take a stab
that you really mean space. In such case

WHERE table1.ID = replace(table2.ID, ' ', '')

may work.

It always help if you post table definitions and sample data, preferably
as CREATE TABLE statements and INSERT statements.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx