How to update single column values in sql server 2005

[Sri Ganesh]

Hi,

I have a one column table like
column1
0
0
0
0
0


And I have another one column table like
column1
1
2
3
4
5

How to update table2 values into table1 value?
When i am select table1 my output like this...
Column1
1
2
3
4
5
Thanks

[Rabbit]

Your thread has been moved to the SQL Server forum.

Why even bother updating the data? Just replace the one table with the other. If all the data you need is in that table, there's no point in having the other table.

[ck9663]

What have you done so far? You can just join the two tables and update one of them.


~~ CK

[Rabbit]

The issue is I see no field on which to join the tables.

[ck9663]

You're correct, Rabbit. Eventually Sri needs to create some sort of key on both tables. In the case of above example, just truncate the target table and insert the source table, no need to make it an update statement using a mock primary key.


~~ CK

[Sri Ganesh]

Hi Rabbit and ck9663 thanks for your replies i cant truncate the table if i want to add one more column in table1 means problem will be occurs, But i got a solution for this..

Thanks
Sri Ganesh

[Rabbit]

Please share your solution in case someone else runs into the same problem.

[Sri Ganesh]

Sure Rabbit this is my solution...

WITH CTE AS
(SELECT ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN1 ASC)AS ROW ,COLUMN1  FROM TABLE1)
UPDATE CTE SET COLUMN1=A.COLUMN1 FROM TABLE2 A WHERE CTE.ROW=A.COLUMN1;

Regards,
Sri Ganesh

[Rabbit]

And this is just a one time update you're doing correct? You're never going to run that query again?