February 25, 2010 at 3:04 pm
I have two query's here. One is a select statement to verify the # of rows I am going to update, the ohter is the actual update statement. I just copied the from statment from the select statement to the from statment in the update statement and got rid of the alias's. For some reason, the select query is returning 3,267 records, and the update query is returning 3,168 rows. Any ideas on what is causing this. Help would be greatly appreciated.... 🙂
select *
from DOCSADM.PROFILE
join tempdata on docsadm.profile.DOCNUMBER = tempdata.dm
join docsadm.a_file_location on docsadm.a_file_location.location = tempdata.fileloc
where docsadm.profile.A_FILE_LOCATION is null
begin tran
update DOCSADM.PROFILE
set DOCSADM.PROFILE.a_file_location = DOCSADM.A_FILE_LOCATION.system_id
from DOCSADM.PROFILE
join tempdata on docsadm.profile.DOCNUMBER = tempdata.dm
join docsadm.a_file_location on docsadm.a_file_location.location = tempdata.fileloc
where docsadm.profile.A_FILE_LOCATION is null
February 25, 2010 at 3:11 pm
The table(s) you aren't updating have more rows than the table you are updating. That's what causes that.
Try this, you'll see what I'm talking about:
CREATE TABLE #T1 (
ID INT IDENTITY PRIMARY KEY,
Col1 CHAR(1));
CREATE TABLE #T2 (
ID INT IDENTITY PRIMARY KEY,
T1ID INT NOT NULL);
INSERT INTO #T1 (Col1)
SELECT 'a';
INSERT INTO #T2 (T1ID)
SELECT 1 UNION ALL
SELECT 1;
SELECT *
FROM #T1 AS T1
INNER JOIN #T2 AS T2
ON T1.ID = T2.T1ID;
UPDATE T1
SET Col1 = 'b'
OUTPUT INSERTED.Col1, INSERTED.ID
FROM #T1 AS T1
INNER JOIN #T2 AS T2
ON T1.ID = T2.T1ID;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2010 at 3:14 pm
Asha.Richardson-1129890 (2/25/2010)
I have two query's here. One is a select statement to verify the # of rows I am going to update, the ohter is the actual update statement. I just copied the from statment from the select statement to the from statment in the update statement and got rid of the alias's. For some reason, the select query is returning 3,267 records, and the update query is returning 3,168 rows. Any ideas on what is causing this. Help would be greatly appreciated.... 🙂select *
from DOCSADM.PROFILE
join tempdata on docsadm.profile.DOCNUMBER = tempdata.dm
join docsadm.a_file_location on docsadm.a_file_location.location = tempdata.fileloc
where docsadm.profile.A_FILE_LOCATION is null
begin tran
update DOCSADM.PROFILE
set DOCSADM.PROFILE.a_file_location = DOCSADM.A_FILE_LOCATION.system_id
from DOCSADM.PROFILE
join tempdata on docsadm.profile.DOCNUMBER = tempdata.dm
join docsadm.a_file_location on docsadm.a_file_location.location = tempdata.fileloc
where docsadm.profile.A_FILE_LOCATION is null
See the parts I bolded. The issue is in your where statement... once the a_file_location was filled in, it didn't get updated again.
I think this would get the same counts:
select DISTINCT DOCSADM.PROFILE.*
from DOCSADM.PROFILE
join tempdata on docsadm.profile.DOCNUMBER = tempdata.dm
join docsadm.a_file_location on docsadm.a_file_location.location = tempdata.fileloc
where docsadm.profile.A_FILE_LOCATION is null
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2010 at 6:37 pm
I am not sure about what you said.... The table I am updating has many more rows than the tables I am not updating..
February 25, 2010 at 6:41 pm
To Wayne:
Is my update statement written correctly?
February 25, 2010 at 6:50 pm
to Wayne:
Basically, your change worked as long as I put in a few column names, but when i just ran your query, I got this message:
Msg 421, Level 16, State 1, Line 4
The text data type cannot be selected as DISTINCT because it is not comparable.
February 25, 2010 at 6:55 pm
Is there a better way to write the select statement to be able to validate the update?
February 25, 2010 at 9:02 pm
Asha.Richardson-1129890 (2/25/2010)
to Wayne:Basically, your change worked as long as I put in a few column names, but when i just ran your query, I got this message:
Msg 421, Level 16, State 1, Line 4
The text data type cannot be selected as DISTINCT because it is not comparable.
Sounds like you have a column with a text datatype in the column list of your select.
Is my update statement written correctly?
Looks good to me.
Is there a better way to write the select statement to be able to validate the update?
This might work:
;WITH CTE AS
(
select DISTINCT <Primary Key Columns from the DOCSADM.PROFILE table>
from DOCSADM.PROFILE
join tempdata on docsadm.profile.DOCNUMBER = tempdata.dm
join docsadm.a_file_location on docsadm.a_file_location.location = tempdata.fileloc
where docsadm.profile.A_FILE_LOCATION is null
)
SELECT p1.*
FROM DOCSADM.PROFILE p1
JOIN CTE
ON CTE.PKColumn1 = p1.PKColumn1
AND CTE.PKColumn2 = p1.PKColumn2
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 26, 2010 at 7:21 am
Asha.Richardson-1129890 (2/25/2010)
I am not sure about what you said.... The table I am updating has many more rows than the tables I am not updating..
It's not a question of which has more rows. It's a question of the join math. Add some more rows to #T1 in my sample code, you'll see what I mean.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply