December 21, 2009 at 6:26 pm
I am trying to do an update with a Select Distinct. If I just do the Distinct portion of it I can get the record I need but the minute I put the update in the code, it disregards it and tries to update numerous records. please see code below, any Ideas why this doesn't work? Thanks for any answers!
CREATE PROCEDURE sp_DataTest
@fName as varchar(50),
@lName as varchar(50),
@pstrClass as varchar(50),
@strTranDateFrm as char(06),
@strTranDateTo as char(06),
@pstrCon as integer,
@pstrAtt as integer
AS
update Trn_Registrations
set Confirmed = @pstrCon, Attended=@pstrAtt from Trn_Registrations
where exists
(SELECT DISTINCT Confirmed, Attended,R.ParticipantID,NameFirst, NameLast,C.ClassID,Title
From Trn_Registrations R, Trn_Participants P, Trn_Schedule S, Trn_Classes C
--join to participants and Registrations
WHERE P.NameFirst = @fName and NameLast = @lName
AND P.ParticipantID = R.ParticipantID
--join registrations to schedule
AND R.ScheduleID = S.ScheduleID
AND CONVERT(VARCHAR(8), TrnDateTime, 12) >= @strTranDateFrm
AND CONVERT(VARCHAR(8), TrnDateTime, 12) <= @strTranDateTo
--join schedule to classes
AND S.ClassID = C.ClassID
AND C.Title = @pstrClass)
GO
December 21, 2009 at 8:32 pm
The query is performing what is it supposed to be doing.
The mistake in your query is that you must be using something like a derived table or a temp table and Join it with the table you want to update using Joins rather than using where exists.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 22, 2009 at 2:13 am
You're not referring to the table that you're updating anywhere in the EXISTS so if that subquery returns any rows at all, all rows of the table that's being updated will be updated.
If you want to use EXISTS, there just be a where clause predicate in the subquery that relates the table listed in the FROM clause of the update to the tables in the subquery.
Something like this
UPDATE s
SET SomeColumn = SomeOtherColumn
FROM SomeTable s
WHERE EXISTS
(SELECT 1 FROM AnotherTable a WHERE a.Col1 = s.column1)
Make sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2009 at 11:42 am
All right that was a simple fix! Thanks Gila Monster. check it out here are the changes I made
CREATE PROCEDURE sp_DataTest
@fName as varchar(50),
@lName as varchar(50),
@pstrClass as varchar(50),
@strTranDateFrm as char(06),
@strTranDateTo as char(06),
@pstrCon as integer,
@pstrAtt as integer
AS
update R
set Confirmed = @pstrCon, Attended=@pstrAtt from Trn_Registrations R
where exists
(SELECT Confirmed, Attended,R.ParticipantID,NameFirst, NameLast,C.ClassID,Title
From Trn_Participants P, Trn_Schedule S, Trn_Classes C
--join to participants and Registrations
WHERE P.NameFirst = @fName and NameLast = @lName
AND P.ParticipantID = R.ParticipantID
--join registrations to schedule
AND R.ScheduleID = S.ScheduleID
AND CONVERT(VARCHAR(8), TrnDateTime, 12) >= @strTranDateFrm
AND CONVERT(VARCHAR(8), TrnDateTime, 12) <= @strTranDateTo
--join schedule to classes
AND S.ClassID = C.ClassID
AND C.Title = @pstrClass)
GO
And I took out the Table in the Select statement. thanks again for everybodys help.
December 22, 2009 at 1:44 pm
You don't need the additional reference to Trn_Participants within the Exists. Also, exists just checks row's existence. It doesn't actually return any columns, hence no need for the long select clause.
In fact, I would reformulate the entire thing. Proper joins and no unnecessary tables.
update R
set Confirmed = @pstrCon, Attended=@pstrAtt from Trn_Registrations R
where
R.NameFirst = @fName and NameLast = @lName
AND EXISTS
(SELECT 1
From Trn_Schedule S INNER JOIN Trn_Classes C ON S.ClassID = C.ClassID
WHERE R.ScheduleID = S.ScheduleID
AND CONVERT(VARCHAR(8), TrnDateTime, 12) >= @strTranDateFrm
AND CONVERT(VARCHAR(8), TrnDateTime, 12) <= @strTranDateTo
AND C.Title = @pstrClass)
I don't like the conversions on the date columns. The prevent index seeks on those columns and, witrh a proper inequality shouldn't be necessary. Can you explain more the dates involved from the table and the parameters?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2009 at 3:24 pm
Unfortunately I did not design these tables, the Keys are not of any use on them.
In order to get to the to fields I need to update, I need to get to the correct record.
To do that I have to go thru multiple tables, my input is name, date(spread) and Class. in order to get the correct table, I need to match up the name from the Participant table to get the ParticipantID to Match up to the Registration Table, the ParticipantID is not Unique so I need to match up to the Schedule Table with the ScheduleID from the Registration Table, which is not Unique either, this is where the Date comes from, I do the convert because the Date is a time date stamp so I strip off the time to match up with the dates. then I match the ClassID with the ClassId in Classes(because the date is not unique either) thus I am assured I have the correct ScheduleID and ParticipantID this puts me on the correct record to do the update. If it were me I would have been alot more particular on how the keys were setup, it would have been better if either the ScheduleID or the ParticipantID could have been used. Thanks again for you input I like what you put together, it looks good but the Name doesn't come from the Registration Table. I would show you the layouts but .. since i am working for the state and we do the processing for Medicaid, HIPAA violations could be involved.
December 23, 2009 at 2:48 am
Sorry, I misread a table name.
Seeing as some of the tables in the exists don't join together, I'd prefer to split it apart.
Which table are those datetime columns in? There's no table prefix for me to tell. Assuming they're in the schedule table.
update R
set Confirmed = @pstrCon, Attended=@pstrAtt from
Trn_Registrations R INNER JOIN Trn_Participants P ON P.ParticipantID = R.ParticipantID
where P.NameFirst = @fName and P.NameLast = @lName
AND exists
(SELECT 1
From Trn_Schedule S INNER JOIN Trn_Classes C on S.ClassID = C.ClassID
--join to participants and Registrations
WHERE R.ScheduleID = S.ScheduleID -- correlation with outer tables
AND CONVERT(VARCHAR(8), S.TrnDateTime, 12) >= @strTranDateFrm
AND CONVERT(VARCHAR(8), S.TrnDateTime, 12) <= @strTranDateTo -- assuming these are from TRN_Schedule
AND C.Title = @pstrClass)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2009 at 3:35 am
Back to basics...
-- 1. Write a query which returns the rows which you want updated from the table you want to update.
SELECT DISTINCT Confirmed, Attended, R.ParticipantID, NameFirst, NameLast, C.ClassID, Title
From Trn_Registrations R, Trn_Participants P, Trn_Schedule S, Trn_Classes C
--join to participants and Registrations
WHERE P.NameFirst = @fName and NameLast = @lName
AND P.ParticipantID = R.ParticipantID
--join registrations to schedule
AND R.ScheduleID = S.ScheduleID
AND CONVERT(VARCHAR(8), TrnDateTime, 12) >= @strTranDateFrm
AND CONVERT(VARCHAR(8), TrnDateTime, 12) <= @strTranDateTo
--join schedule to classes
AND S.ClassID = C.ClassID
AND C.Title = @pstrClass
-- 2. Write it properly, check it works - the date arithmetic is different
-- Strip time component if necessary
SELECT @strTranDateFrm = DATEADD(dd,DATEDIFF(dd,0,CAST(@strTranDateFrm AS DATETIME)),0) -- NO TIME
SELECT @strTranDateTo = DATEADD(dd,DATEDIFF(dd,0,CAST(@strTranDateTo AS DATETIME)),1) -- <-- NEXT DAY, NO TIME
SELECT DISTINCT Confirmed, Attended, R.ParticipantID, NameFirst, NameLast, C.ClassID, Title
FROM Trn_Registrations R
INNER JOIN Trn_Participants P ON P.ParticipantID = R.ParticipantID
INNER JOIN Trn_Schedule S ON R.ScheduleID = S.ScheduleID
INNER JOIN Trn_Classes C ON S.ClassID = C.ClassID
WHERE P.NameFirst = @fName
and NameLast = @lName
AND TrnDateTime >= @strTranDateFrm
AND TrnDateTime < @strTranDateTo -- < CHANGED OPERATOR
AND C.Title = @pstrClass
-- 3. Turn it into an UPDATE...FROM
UPDATE R SET Confirmed = @pstrCon, Attended = @pstrAtt
FROM Trn_Registrations R
INNER JOIN Trn_Participants P ON P.ParticipantID = R.ParticipantID
INNER JOIN Trn_Schedule S ON R.ScheduleID = S.ScheduleID
INNER JOIN Trn_Classes C ON S.ClassID = C.ClassID
WHERE P.NameFirst = @fName
and p.NameLast = @lName
AND TrnDateTime >= @strTranDateFrm
AND TrnDateTime < @strTranDateTo
AND C.Title = @pstrClass
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 23, 2009 at 4:12 pm
GilaMonster (12/23/2009)
Sorry, I misread a table name.Seeing as some of the tables in the exists don't join together, I'd prefer to split it apart.
Which table are those datetime columns in? There's no table prefix for me to tell. Assuming they're in the schedule table.
update R
set Confirmed = @pstrCon, Attended=@pstrAtt from
Trn_Registrations R INNER JOIN Trn_Participants P ON P.ParticipantID = R.ParticipantID
where P.NameFirst = @fName and P.NameLast = @lName
AND exists
(SELECT 1
From Trn_Schedule S INNER JOIN Trn_Classes C on S.ClassID = C.ClassID
--join to participants and Registrations
WHERE R.ScheduleID = S.ScheduleID -- correlation with outer tables
AND CONVERT(VARCHAR(8), S.TrnDateTime, 12) >= @strTranDateFrm
AND CONVERT(VARCHAR(8), S.TrnDateTime, 12) <= @strTranDateTo -- assuming these are from TRN_Schedule
AND C.Title = @pstrClass)
the Date fields are in the Schedule table. thanks for this alternative, I am going to try this one as well looks good and to the point. Another thing and I know this is a bone of contention with alot of programmers, the use of inner joins are faster but my tables are so small the difference is negligible. thanks again for your help!
December 23, 2009 at 4:17 pm
Chris Morris-439714 (12/23/2009)
Back to basics...
-- 1. Write a query which returns the rows which you want updated from the table you want to update.
SELECT DISTINCT Confirmed, Attended, R.ParticipantID, NameFirst, NameLast, C.ClassID, Title
From Trn_Registrations R, Trn_Participants P, Trn_Schedule S, Trn_Classes C
--join to participants and Registrations
WHERE P.NameFirst = @fName and NameLast = @lName
AND P.ParticipantID = R.ParticipantID
--join registrations to schedule
AND R.ScheduleID = S.ScheduleID
AND CONVERT(VARCHAR(8), TrnDateTime, 12) >= @strTranDateFrm
AND CONVERT(VARCHAR(8), TrnDateTime, 12) <= @strTranDateTo
--join schedule to classes
AND S.ClassID = C.ClassID
AND C.Title = @pstrClass
-- 2. Write it properly, check it works - the date arithmetic is different
-- Strip time component if necessary
SELECT @strTranDateFrm = DATEADD(dd,DATEDIFF(dd,0,CAST(@strTranDateFrm AS DATETIME)),0) -- NO TIME
SELECT @strTranDateTo = DATEADD(dd,DATEDIFF(dd,0,CAST(@strTranDateTo AS DATETIME)),1) -- <-- NEXT DAY, NO TIME
SELECT DISTINCT Confirmed, Attended, R.ParticipantID, NameFirst, NameLast, C.ClassID, Title
FROM Trn_Registrations R
INNER JOIN Trn_Participants P ON P.ParticipantID = R.ParticipantID
INNER JOIN Trn_Schedule S ON R.ScheduleID = S.ScheduleID
INNER JOIN Trn_Classes C ON S.ClassID = C.ClassID
WHERE P.NameFirst = @fName
and NameLast = @lName
AND TrnDateTime >= @strTranDateFrm
AND TrnDateTime < @strTranDateTo -- < CHANGED OPERATOR
AND C.Title = @pstrClass
-- 3. Turn it into an UPDATE...FROM
UPDATE R SET Confirmed = @pstrCon, Attended = @pstrAtt
FROM Trn_Registrations R
INNER JOIN Trn_Participants P ON P.ParticipantID = R.ParticipantID
INNER JOIN Trn_Schedule S ON R.ScheduleID = S.ScheduleID
INNER JOIN Trn_Classes C ON S.ClassID = C.ClassID
WHERE P.NameFirst = @fName
and p.NameLast = @lName
AND TrnDateTime >= @strTranDateFrm
AND TrnDateTime < @strTranDateTo
AND C.Title = @pstrClass
I guess my original was probably not clear enough, but the input date fields are not date time stamp there are from the program. and I would need a <= on the "To Date" field because the field would include the time the user would be looking at. thanks for the great examples, I am definitely keeping these snippets for future coding. much appreciated.
December 24, 2009 at 12:43 am
stephen.rice 10158 (12/23/2009)
Another thing and I know this is a bone of contention with alot of programmers, the use of inner joins are faster but my tables are so small the difference is negligible.
They're not.
Joins in the from clause and joins in the where clause produce the same execution plan hence the same performance. The reasons for using the explicit join syntax is because it's much, much harder to accidentally miss a join criteria when you're doing explicit joins. Also, outer joins are no longer possible with the join in the where clause. The *= syntax was deprecated in SQL 2000 and removed in SQL 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy