?
Solved

update each row without where clause

Posted on 2012-04-04
12
Medium Priority
?
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 576 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 1140 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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 1140 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
 
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 1140 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 32

Assisted Solution

by:awking00
awking00 earned 284 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 1140 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 576 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

719 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