Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update Query

Author

Topic

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-05 : 02:14:47

I have Table_A and Table_B. Both tables have a column called CCDB_CUST_ID.

I will like to update DOB of Table_A with DOB of Table_B, using CCDB_CUST_ID as primary key.

I have come up with this query:
Update Table_A
Set Table_A.DOB = Table_B.DOB
WHERE Table_A.CCDB_CUST_ID IN (select CCDB_CUST_ID from Table_B)

I have millions of records in my table, and this can't go wrong. Hence, can anyone tell me whether my query has any problem?

Thanks alot!

- HELP -

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-05 : 02:32:01

i am not an expert (so u should wait for another answer as well) but i think the following statements works.

update tableA
set tableA.dob = tableB.dob
from tableA
join tableB on tableA.CCDB_CUST_ID = tableB.CCDB_CUST_ID

nevertheless, u should backup your database before running any statement ;)

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-05 : 02:58:01

Using my own query, it says:
The column prefix 'Table_B' does not match with a table name or alias name used in the query.

Help.......

- HELP -

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-05 : 02:59:03

quote:
Originally posted by dev45

i am not an expert (so u should wait for another answer as well) but i think the following statements works.

update tableA
set tableA.dob = tableB.dob
from tableA
join tableB on tableA.CCDB_CUST_ID = tableB.CCDB_CUST_ID

nevertheless, u should backup your database before running any statement ;)

Thx... I will give it a try....

- HELP -

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-05 : 04:06:10

try this:

update t1
set dob = tableB.dob
from tableA t1
join tableB t2 on t1.CCDB_CUST_ID = t2.CCDB_CUST_ID

Go with the flow & have fun! Else fight the flow

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-05 : 06:05:49

quote:
Originally posted by spirit1

try this:

update t1
set dob = tableB.dob
from tableA t1
join tableB t2 on t1.CCDB_CUST_ID = t2.CCDB_CUST_ID

Go with the flow & have fun! Else fight the flow

t1 refers to...?
t2 refers to...?

Thx for helping out!

- HELP -

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-10-05 : 06:08:30

t1 is as alias for tableA
t2 an alias for tableB
it's the same thing if you use either

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-05 : 06:08:59

never heard of aliases???

t1 refers to tableA
t2 refers to tableB

from tableA t1 -- this is an alias
join tableB t2 -- so is this

Go with the flow & have fun! Else fight the flow

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-05 : 21:24:01

I see... Thx Thx Thx! ^-^

- HELP -

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources