How to update A Single Column data from one table into another..

[chiragvithlani]

In SQL Server 2005... with some 11K rows

I am having two tables T1 and T2(as bkup of T1), now i need one column data of T2 as i accidently updated T1 column data, (Update T1.colx with T2.colx)

I tried with

<code> update T1 set colx=(select colx from T2) </code>

but this is syntactically wrong as subquery returns multiple rows,

If i try with..

<code> insert into T1 (colx) select T2.colx from T2 as T2_1 cross join T2 </code> it will append and i dont need to append but update....

Copy paste is also not working... Can some one help !!!

[Jim Doherty]

In SQL Server 2005... with some 11K rows

I am having two tables T1 and T2(as bkup of T1), now i need one column data of T2 as i accidently updated T1 column data, (Update T1.colx with T2.colx)

I tried with

<code> update T1 set colx=(select colx from T2) </code>

but this is syntactically wrong as subquery returns multiple rows,

If i try with..

<code> insert into T1 (colx) select T2.colx from T2 as T2_1 cross join T2 </code> it will append and i dont need to append but update....

Copy paste is also not working... Can some one help !!!

 UPDATE t1
SET t1.colx=t2.colx
FROM t1,t2

Regards

Jim :)

[chiragvithlani]

hello jim,

the code above you mentioned just updated the destination column with only First Row Value.... !!!!!! i.e. 'yes' of my T2.colx

yes
yes
yes

But My Table Column Contained Values like...

yes
no
none (etc..)

your suggested code updated all T1.colx with 'yes' where i have different values for 11k rows....

Can you tell what must be the problem and how to update T1.colx from T2.colx

Thanks 1`s again !!

Regards,

[amitpatel66]

hello jim,

the code above you mentioned just updated the destination column with only First Row Value.... !!!!!! i.e. 'yes' of my T2.colx

yes
yes
yes

But My Table Column Contained Values like...

yes
no
none (etc..)

your suggested code updated all T1.colx with 'yes' where i have different values for 11k rows....

Can you tell what must be the problem and how to update T1.colx from T2.colx

Thanks 1`s again !!

Regards,

You need to add WHERE condition between t1 and t2 to update accordingly.

UPDATE t1
SET t1.colx=t2.colx
FROM t1,t2
WHERE t1.col1 = t2.col1
 

[Jim Doherty]

You need to add WHERE condition between t1 and t2 to update accordingly.

UPDATE t1
SET t1.colx=t2.colx
FROM t1,t2
WHERE t1.col1 = t2.col1
 

Thanks Amit you got there before me

Jim :)

[chiragvithlani]

Thank you Amit,

your code worked, you know i am just learning SQL... Can you suggest me few links where i can get my basics(fundametals) of SQL, I think it got be sounder then i am at....

Regards,

Chirag

[amitpatel66]

Thank you Amit,

your code worked, you know i am just learning SQL... Can you suggest me few links where i can get my basics(fundametals) of SQL, I think it got be sounder then i am at....

Regards,

Chirag

check out here for SQL fundamentals