Help me! UPDATE command - 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)

Help me! UPDATE command

Author

Topic

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-08 : 07:34:54

Hi all!

I have two table USER1 includes field : ID, Username,Password and Table USER2 includes filed : ID ,Username,Name . When user register . Data will save into 2 that table . One reason or other, two filed ID on the two table not same but Username on the 2 table same . I want UPDATE all ID on the User1 = ID on the User2 .Please help meeeeeeeeeeee

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-08 : 08:20:08

update table1
set id = t2.id
from table1 t1
join table 2 t2
on t1.username = t2.username

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vikrant99
Starting Member

2 Posts

Posted - 2006-09-08 : 08:20:41

You may try the following

1. create a new table say USER3 same as table USER1

2. insert into USER3 values (select USER2.ID, USER1.Username, USER1.Password from USER1, USER2 where USER1.Username=USER2.Username)

--- check the insert command syntax

3. check the data of USER3 table for desired result

4. drop table USER1

5. rename table USER3 as USER1

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-08 : 09:46:10

You have this problem because you have a bad table design.

If you just had a single table like the following, you couldn't have this problem:
ID, Username, Password, Name

CODO ERGO SUM

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-08 : 14:21:38

quote:
Originally posted by nr

update table1
set id = t2.id
from table1 t1
join table 2 t2
on t1.username = t2.username

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hic!not run!huhuhu

duplicity
Starting Member

4 Posts

Posted - 2006-09-08 : 15:36:59

quote:
Originally posted by haibec

quote:
Originally posted by nr

update table1
set id = t2.id
from table1 t1
join table 2 t2
on t1.username = t2.username

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hic!not run!huhuhu

TRY TO TYPE join table2 t2 not ~~join table 2 t2~~

haibec
Yak Posting Veteran

54 Posts

Posted - 2006-09-08 : 21:40:38

quote:
Originally posted by duplicity

quote:
Originally posted by haibec

quote:
Originally posted by nr

update table1
set id = t2.id
from table1 t1
join table 2 t2
on t1.username = t2.username

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hic!not run!huhuhu

TRY TO TYPE join table2 t2 not ~~join table 2 t2~~

not run!
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-09 : 02:01:55

Somthing like this..??

Update T1 Set T1.[ID] = T2.[ID] From User1 T1 Inner Join T2
On T1.UserName = T2.UserName
Where
T1.[ID] <> T2.[ID]

Or Follow this link and post accordingly

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources