Solved

update each row without where clause

Posted on 2012-04-04
12
319 Views
Last Modified: 2012-04-05
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
Comment
Question by:25112
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 144 total points
ID: 37808765
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
 
LVL 6

Assisted Solution

by:Patrick Tallarico
Patrick Tallarico earned 285 total points
ID: 37808788
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
 
LVL 5

Author Comment

by:25112
ID: 37811354
Tom, that is the idea..
0
 
LVL 5

Author Comment

by:25112
ID: 37811363
stpmt11, i am trying with your idea.. i will let you know
0
 
LVL 5

Author Comment

by:25112
ID: 37811655
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
 
LVL 6

Assisted Solution

by:Patrick Tallarico
Patrick Tallarico earned 285 total points
ID: 37811770
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Author Comment

by:25112
ID: 37812025
isn't the condition where m.column1 <>  t.tempcolumn enough to disqualify where there is a match?
0
 
LVL 6

Assisted Solution

by:Patrick Tallarico
Patrick Tallarico earned 285 total points
ID: 37812116
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 71 total points
ID: 37812166
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
 
LVL 6

Assisted Solution

by:Patrick Tallarico
Patrick Tallarico earned 285 total points
ID: 37812172
@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
 
LVL 27

Accepted Solution

by:
tliotta earned 144 total points
ID: 37814596
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
 
LVL 5

Author Comment

by:25112
ID: 37814869
implemented your suggestion.. many thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now