Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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?
0
25112
Asked:
25112
  • 5
  • 4
  • 2
  • +1
7 Solutions
 
tliottaCommented:
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 TallaricoCommented:
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
 
25112Author Commented:
Tom, that is the idea..
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 TallaricoCommented:
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 TallaricoCommented:
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
 
awking00Commented:
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 TallaricoCommented:
@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
 
tliottaCommented:
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
 
25112Author Commented:
implemented your suggestion.. many thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now