Solved

update each row without where clause

Posted on 2012-04-04
12
323 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
possible to record changes (trigger I think) msql 11 34
Challenging SQL Update 5 41
SQL: launch actions one before the other 10 22
Help Required 3 90
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore 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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

15 Experts available now in Live!

Get 1:1 Help Now