trying to do an update with Distinct

  • 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

  • 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.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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!

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply