25112
asked on
update each row without where clause
I have a table... 12,098 records.. first column is PK
there is a temp table that has 9876 rows (one column only - unique values)
for testing purpose i have to update only the PK in main table for the first 9876 rows with the values from the temp table..
how could you do that in one query in T-SQL?
there is a temp table that has 9876 rows (one column only - unique values)
for testing purpose i have to update only the PK in main table for the first 9876 rows with the values from the temp table..
how could you do that in one query in T-SQL?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
stpmt11, i am trying with your idea.. i will let you know
ASKER
stpmt11, i am getting
error:
Violation of PRIMARY KEY constraint ... Cannot insert duplicate key in object . The duplicate key value is (474157).
how to avoid it?
Update maintable
set column1 = t.tempcolumn
from
(select row_number() OVER (order by column1) as 'rn',* from maintable) as m
inner join
(select row_number() OVER (order by tempcolumn) as rn , tempcolumn from temptable ) as t
on m.rn = t.rn where m.column1 <> t.tempcolumn
error:
Violation of PRIMARY KEY constraint ... Cannot insert duplicate key in object . The duplicate key value is (474157).
how to avoid it?
Update maintable
set column1 = t.tempcolumn
from
(select row_number() OVER (order by column1) as 'rn',* from maintable) as m
inner join
(select row_number() OVER (order by tempcolumn) as rn , tempcolumn from temptable ) as t
on m.rn = t.rn where m.column1 <> t.tempcolumn
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
isn't the condition where m.column1 <> t.tempcolumn enough to disqualify where there is a match?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
implemented your suggestion.. many thanks
ASKER