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?
LVL 5
25112Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
tliottaConnect With a Mentor Commented:
It might be best to create a JOIN of the two tables by row number and INSERT the result into a new temp table. Then clear the original table and rebuild it from the new temp table.

It simply might not be possible to eliminate duplicates any other way. Further, examination of the new temp table would show where all cleanup might be needed.

Tom
0
 
tliottaConnect With a Mentor Commented:
So you want the PK of the first row in your table to be changed to be the value of the first row in your temp table? And then the PK of the second row in your table to be changed to be the value of the second row from your temp table? And so on, until the temp table is exhausted?

Tom
0
 
Patrick TallaricoConnect With a Mentor FSEP Systems AnalystCommented:
I have to assume that there is no way to link the tables through a join using existing data, therefore, can you order the Main table in such a way that all the values in that table will correctly correspond with the values to be replaced from the temp table?  If you can't order the main table properly, then you might have to create an extra column to help signify the top 9876 that you actually wish to edit. The lack of relational data makes this tricky.

if so, you could try something like the following.  Word of caution, try this on copies of the tables first so as to protect the original data.  

Update maintable
set column1 = t.column1
from (select rownumber() as 'rn',* from maintable order by column1 ) as m
         inner join
         (select rownumber() as 'rn',column1 from temptable ) as t
     on m.rn = t.rn

This query should assign a row number to the main table properly ordered to match the temp table.  You should be able to add the Order By into the select statement for the temp table too if you need.  Also you can adjust the order by in the select statement for the main table to order it by any column you wish.

Another less friendly approach would be to use a cursor to move through the records of the main table one by one, but again, this would require that both tables be ordered properly to ensure the proper replacement values.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
25112Author Commented:
Tom, that is the idea..
0
 
25112Author Commented:
stpmt11, i am trying with your idea.. i will let you know
0
 
25112Author Commented:
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
0
 
Patrick TallaricoConnect With a Mentor FSEP Systems AnalystCommented:
it sounds like there are values in the temp table that match values that would remain in the table after the update.  Perhaps you should compare the temp table to the main table to see how many matching values the tables contain.  You could replace the main table's key values with the rownumber values first, then try the update.  This is assuming that the only matching keys appear in the first 9876 rows.
0
 
25112Author Commented:
isn't the condition where m.column1 <>  t.tempcolumn enough to disqualify where there is a match?
0
 
Patrick TallaricoConnect With a Mentor FSEP Systems AnalystCommented:
Because you are matching the row number columns, the query doesn't compare the two table's key values, with the exception of if they have the same column number.  
1,23456
2,34567
3,45678

1,67890
2,34567
3,56789
4,23456
If we take the first list as the temp table, and the second as the main, we can expect the where clause to catch the second row, since both the rows match, but it would still error out from the duplicate column value in row 4 of the main table matching the row 1 value of the temp table.
I suggest running a query on the two tables to first compare the keys of both tables and see what matches come up.
0
 
awking00Connect With a Mentor Commented:
As has been explained, there is likely a value in a row of the temp table that matches a value for the primary key in the main table that is in a higher row, causing a primary key violation. You might try altering the table to remove the primary key constraint, do the update, then re-create the primary constraint.
0
 
Patrick TallaricoConnect With a Mentor FSEP Systems AnalystCommented:
@awking has a good idea, just a caution, however, you may end up with duplicate values in the key column that you would then have to resolve before you could add the primary key back.
0
 
25112Author Commented:
implemented your suggestion.. many thanks
0
All Courses

From novice to tech pro — start learning today.