July 6, 2010 at 1:44 pm
Hello,
I am running the following update command.
UPDATE P
SET P.Specialty = T.Specialty
FROM SomeTable P
LEFT JOIN temp T
ON P.id = T.id
WHERE P.Specialty <> T.Specialty
AND T.Specialty IS NOT NULL
AND T.id IS NOT NULL
It finishes execution, and shows that 2327 records updated (in every run I do, I have executed this 5 times now).
It doesn't update the table though, atleast not all the records. Its a huge table with more than 1 million records.
Can anyone help me pls ?
Thanks in advance.
July 6, 2010 at 11:58 pm
If updation has happened, following query should give same output for both selected columns
SELECT P.Specialty , T.Specialty
FROM SomeTable P
LEFT JOIN temp T
ON P.id = T.id
WHERE P.Specialty <> T.Specialty
AND T.Specialty IS NOT NULL
AND T.id IS NOT NULL if yes it means query worked as it is written.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 7, 2010 at 12:45 am
You have a join and a where clause. Most chances are that both (the join and where criteria) filter the records that should be effected from your update query.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 7, 2010 at 11:10 am
Thanks for the query Bhuvnesh. I ran it and saw that there are records which are not updated.
Adi, you are right, the records are filtered and I am updating only those records where the specialty is not same. Please correct me if you see any flaw in the query.
MY Q is, why do I see same number of records everytime when I run the update command, as in after one full update, it should say '0 records updated', but in every run I see 'x records update' where x is same number again and again.
Something is wrong and I am unable to see it.
Please help.
July 7, 2010 at 10:23 pm
touchmeknot (7/7/2010)
Thanks for the query Bhuvnesh. I ran it and saw that there are records which are not updated.Adi, you are right, the records are filtered and I am updating only those records where the specialty is not same. Please correct me if you see any flaw in the query.
MY Q is, why do I see same number of records everytime when I run the update command, as in after one full update, it should say '0 records updated', but in every run I see 'x records update' where x is same number again and again.
Something is wrong and I am unable to see it.
Please help.
I see your point, and have to admit that you are correct. I would also expect the query to update any number of records only after the first time that I'd run it. Since it updates the same number of rows each time, I would check one of those things:
1) Did you use set rowcount 2327 along the way? If you did, then each time that you run your update statement, it will only effect 2327 rows even if more rows should be effected. If this is the case just run set rowcount 0 statement.
2) Do you have rollback transaction somewhere in the code? If you do, then this can explain why you keep updating the same records.
3) Do you have an instead of trigger on this table that modifies the update statement?
4) Do you have a trigger on the table that updates those record the second time (just after your update statement runs) or maybe modifies the data in the table that you use for the join operator?
5) Could it be that something else is also running and updating the table after your update statement?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 8, 2010 at 7:42 pm
Thanks Adi for such a detailed solution. I am running the simple update which I have written above.
The problem was that the TEMP table had duplicate id's. I did not create any key, just created temp table and loaded data, thus resulting in duplicates.
However, still did not find the solution for the number 'x', why does it update everytime I run it.
July 9, 2010 at 3:08 am
what does this do:
UPDATE P
SET P.Specialty = T.Specialty
FROM SomeTable P
INNER JOIN temp T
ON P.id = T.id
WHERE P.Specialty <> T.Specialty
July 9, 2010 at 5:14 am
Looks like every time you populate your temp table, you get fresh records which meets <> criteria and gets updated.
It could be of help if you can paste complete code(where you are populating the temp table)
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
July 9, 2010 at 6:20 am
Like Rishi, I don't understand why you were doing a left join in the first place, but there is something you are not telling us. Your original code works correctly with the following sample data. If you run it and look at the messages, it updates 5 rows the first time and zero rows the second time. Like the previous post suggested, if you are reloading the temp table with new data every time, that could be the problem right there. But that is just a guess, not a hard answer.
When you run into problems like this, bring it down to a scale that is comprehensible and start testing your assumptions one at a time. (It only took me 5 minutes to set up and run the code posted below.)
The example proves that your code works in a controlled situation. Now you need numbers about your data and facts about your environment to figure out what your query is actually doing. SQL is unlike other languages in that queries behave differently depending on the environment. This is why most of us always ask for queries to be accompanied by table schema, and some sample data.
Some questions for starters (with apologies to Adi Cohn for any duplication):
Exactly how many rows are in both tables in your situation?
Is ID in fact a unique Key in "sometable"?
How many rows have the same id?
Is your data static or changing between tests?
Is the update included in a transaction that is getting rolled back?
Are there triggers in place that could interrupt the update of SomeTable?
set nocount on;
if object_id(N'Sometable') is not null drop table sometable
if object_id(N'Temp') is not null drop table temp
create table sometable (id int primary key, specialty varchar(10))
create table temp (id int primary key, specialty varchar(10))
insert into sometable
select 1,'SQL' union all
select 2,'SQL' union all
select 3,'SQL' union all
select 4,'SQL' union all
select 5,'SQL' union all
select 6,'SQL' union all
select 7,'SQL' union all
select 8,'SQL' union all
select 9,'SQL'
insert into temp
select 1,'VB' union all
select 2,'C' union all
select 3,'VB' union all
select 4,'C' union all
select 5,'VB'
select 'Temp',* from Temp
select 'SomeTable - Before',* from sometable P
set nocount off;
print ' '
print '-- First Update -- '
UPDATE P
SET P.Specialty = T.Specialty
FROM SomeTable P
LEFT JOIN temp T
ON P.id = T.id
WHERE P.Specialty <> T.Specialty
AND T.Specialty IS NOT NULL
AND T.id IS NOT NULL
print ' '
print '-- Second Update -- '
UPDATE P
SET P.Specialty = T.Specialty
FROM SomeTable P
LEFT JOIN temp T
ON P.id = T.id
WHERE P.Specialty <> T.Specialty
AND T.Specialty IS NOT NULL
AND T.id IS NOT NULL
set nocount on;
select 'Sometable - After',* from sometable P
set nocount off;
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply