Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jan 2013
    Posts
    19

    Unanswered: Need QUERY for DELETE

    I have a query to select list of patients based on some condition. But I need to delete those instead of selecting. Here is the query:

    SELECT
    [p].[Patient_ID],
    [p].[Last_Name],
    [p].[First_Name],
    [p].[MI]
    FROM [Coupon].[dbo].[Patient] AS [p]
    JOIN [Coupon].[dbo].[Patient_PLPPrescriberSite_Xref] AS [ppsx] ON ([ppsx].[PatientID]=[p].[Patient_ID])
    JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx] ON ([psx].[PrescriberSiteXrefID]=[ppsx].[HCP_PLP_EnrollmentPrescriberSiteXrefID])
    JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps] ON ([ps].[PrescriberSiteID]=[psx].[PrescriberPersonID])
    JOIN [TCPLP].[dbo].[Person] AS [ph] ON [PersonID]=[psx].[PrescriberPersonID]

    WHERE
    ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
    OR ([ph].[LastName] IN ('n/a', 'N/A', ' ')) OR ([ph].[LastName] LIKE '0%')

    SELECT
    [p].[PersonID],
    [p].[LastName],
    [p].[FirstName],
    [p].[MiddleName]

    FROM
    [R2].[dbo].[Person] AS [p]
    JOIN [R2].[dbo].[R2EnrollmentSiteDetails] AS [esd] ON ([esd].[PatientID]=[p].[PersonID])
    JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx] ON ([psx].[PrescriberSiteXrefID]=[esd].[TP_PLPPrescriberSiteXrefID])
    JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps] ON ([ps].[PrescriberSiteID]=[psx].[PrescriberSiteID])
    JOIN [TCPLP].[dbo].[Person] AS [ph] ON ([ph].[PersonID]=[psx].[PrescriberPersonID])

    WHERE
    ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
    OR ([ph].[LastName] IN ('n/a', 'N/A', ' ')) OR ([ph].[LastName] LIKE '0%')




    Can anyone tell me the query plzzz

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT [p].[Patient_ID], [p].[Last_Name], [p].[First_Name], [p].[MI]
       FROM [Coupon].[dbo].[Patient] AS [p]
       JOIN [Coupon].[dbo].[Patient_PLPPrescriberSite_Xref] AS [ppsx]
          ON ([ppsx].[PatientID] = [p].[Patient_ID])
       JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx]
          ON ([psx].[PrescriberSiteXrefID]
    =        [ppsx].[HCP_PLP_EnrollmentPrescriberSiteXrefID])
       JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps]
          ON ([ps].[PrescriberSiteID] = [psx].[PrescriberPersonID])
       JOIN [TCPLP].[dbo].[Person] AS [ph]
          ON [PersonID ]= [psx].[PrescriberPersonID]
    WHERE ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
       OR ([ph].[LastName]    IN ('n/a', 'N/A', ' ')) OR ([ph].[LastName] LIKE '0%')
    
    SELECT [p].[PersonID], [p].[LastName], [p].[FirstName], [p].[MiddleName]
       FROM [R2].[dbo].[Person] AS [p]
       JOIN [R2].[dbo].[R2EnrollmentSiteDetails] AS [esd]
          ON ([esd].[PatientID] = [p].[PersonID])
       JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx]
          ON ([psx].[PrescriberSiteXrefID] = [esd].[TP_PLPPrescriberSiteXrefID])
       JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps]
          ON ([ps].[PrescriberSiteID] = [psx].[PrescriberSiteID])
       JOIN [TCPLP].[dbo].[Person] AS [ph]
          ON ([ph].[PersonID] = [psx].[PrescriberPersonID])
       WHERE ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
          OR ([ph].[LastName] IN ('n/a', 'N/A', ' '))    OR ([ph].[LastName] LIKE '0%')
    First verify that these statements are exactly the same as your statements... I've just reformatted them so that they are a bit easier to read.

    Once you're sure that these produce what you want, replace the RED SELECT list with the word DELETE and you'll be ready to go!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    19

    Delete

    I have already tried that one but it doesn't work. It says "invalid object name" after DELETE

  4. #4
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18
    SqlTcard,

    This may be coming from a delete TRIGGER that's associated to the table that you're deleting. If there is an associated trigger, the trigger in question may be referring to an object that's no longer present in your database.

    YesAgile

  5. #5
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18
    SqlTcard,

    Forgot to ask...are you getting this message when you parse the statement or is this being thrown after the DELETE statement is executed?

    YesAgile

  6. #6
    Join Date
    Jan 2013
    Posts
    19

    @YesAgile

    While Phrasing the statement itself... first of all its not getting executed because of that


    Thanks

  7. #7
    Join Date
    Jan 2013
    Posts
    19
    Check with the attachment

    Thanks
    Last edited by SqlTcard; 05-01-13 at 00:58.

  8. #8
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18
    SqlTcard,

    OK. Looks like you have an IntelliSense cache issue. This is not a problem that's coming from SQL Server but rather SQL Server Management Studio (SSMS). My bet is that if you were to try and execute the query it would still work. You're seeing those red lines because SSMS is complaining that it can't resolve those objects in your database. However, the script will still execute and be resolved once sent to SQL.

    You can always use Ctrl + Shift + R to refresh the local intellisense cache or go to Edit | IntelliSense | Refresh. That should take care of the red lines you're seeing.

    YesAgile

  9. #9
    Join Date
    Jan 2013
    Posts
    19
    YesAgile,

    I don't remember exactly what the error message was... I tried to execute the query but it was not executed... So the difference between the SELECT QUERY & DELETE QUERY statements is just a word "DELETE" ????

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by SqlTcard View Post
    I have already tried that one but it doesn't work. It says "invalid object name" after DELETE
    Go back, re-read my directions, try again. You were pretty close but only got part of the line.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18
    SqlTcard,

    Sorry, long day. Didn't see your SQL screen shot for what it was. Do as Pat says. Remove your selelect statement in red and simply replace it with the word DELETE. SQL Server is complaining because columns are only valid when you're returning data. There are no rows to return when you're deleting data.

    Code:
    SELECT [p].[Patient_ID], [p].[Last_Name], [p].[First_Name], [p].[MI]
       FROM [Coupon].[dbo].[Patient] AS [p]
       JOIN [Coupon].[dbo].[Patient_PLPPrescriberSite_Xref] AS [ppsx]
          ON ([ppsx].[PatientID] = [p].[Patient_ID])
       JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx]
          ON ([psx].[PrescriberSiteXrefID]
    =        [ppsx].[HCP_PLP_EnrollmentPrescriberSiteXrefID])
       JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps]
          ON ([ps].[PrescriberSiteID] = [psx].[PrescriberPersonID])
       JOIN [TCPLP].[dbo].[Person] AS [ph]
          ON [PersonID ]= [psx].[PrescriberPersonID]
    WHERE ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
       OR ([ph].[LastName]    IN ('n/a', 'N/A', ' ')) OR ([ph].[LastName] LIKE '0%')
    should be re-stated as:

    Code:
    --SELECT [p].[Patient_ID], [p].[Last_Name], [p].[First_Name], [p].[MI]
    DELETE
       FROM [Coupon].[dbo].[Patient] AS [p]
       JOIN [Coupon].[dbo].[Patient_PLPPrescriberSite_Xref] AS [ppsx]
          ON ([ppsx].[PatientID] = [p].[Patient_ID])
       JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx]
          ON ([psx].[PrescriberSiteXrefID]
    =        [ppsx].[HCP_PLP_EnrollmentPrescriberSiteXrefID])
       JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps]
          ON ([ps].[PrescriberSiteID] = [psx].[PrescriberPersonID])
       JOIN [TCPLP].[dbo].[Person] AS [ph]
          ON [PersonID ]= [psx].[PrescriberPersonID]
    WHERE ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
       OR ([ph].[LastName]    IN ('n/a', 'N/A', ' ')) OR ([ph].[LastName] LIKE '0%')

  12. #12
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18
    SqlTcard,

    Meant to say that column names are not valid when you're DELETEing data.

    YesAgile

  13. #13
    Join Date
    Jan 2013
    Posts
    19
    YesAgile & Pat Phelan,

    Sorry guys I'm new to the environment. I thought I just need to put the DELETE keyword in place of SELECT. I haven't removed the column names from the previous query!!!! First thing in the morning I'll execute it hope everything works fine....


    Thanks both of you

  14. #14
    Join Date
    Jan 2013
    Posts
    19

    Didn't work

    YesAgile, Pat

    The DELETE keyword didn't work
    Last edited by SqlTcard; 05-01-13 at 00:58.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Arg! My bad, I missed one step too!
    Code:
    DELETE p
       FROM [Coupon].[dbo].[Patient] AS [p]
       JOIN [Coupon].[dbo].[Patient_PLPPrescriberSite_Xref] AS [ppsx]
          ON ([ppsx].[PatientID] = [p].[Patient_ID])
       JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx]
          ON ([psx].[PrescriberSiteXrefID]
    =        [ppsx].[HCP_PLP_EnrollmentPrescriberSiteXrefID])
       JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps]
          ON ([ps].[PrescriberSiteID] = [psx].[PrescriberPersonID])
       JOIN [TCPLP].[dbo].[Person] AS [ph]
          ON [PersonID ]= [psx].[PrescriberPersonID]
    WHERE ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
       OR ([ph].[LastName]    IN ('n/a', 'N/A', ' ')) OR ([ph].[LastName] LIKE '0%')
    
    DELETE p
       FROM [R2].[dbo].[Person] AS [p]
       JOIN [R2].[dbo].[R2EnrollmentSiteDetails] AS [esd]
          ON ([esd].[PatientID] = [p].[PersonID])
       JOIN [TCPLP].[dbo].[PrescriberSiteXref] AS [psx]
          ON ([psx].[PrescriberSiteXrefID] = [esd].[TP_PLPPrescriberSiteXrefID])
       JOIN [TCPLP].[dbo].[PrescriberSite] AS [ps]
          ON ([ps].[PrescriberSiteID] = [psx].[PrescriberSiteID])
       JOIN [TCPLP].[dbo].[Person] AS [ph]
          ON ([ph].[PersonID] = [psx].[PrescriberPersonID])
       WHERE ([ps].[Description] IN ('n/a', 'N/A', ' ')) OR ([ps].[Description] LIKE '0%')
          OR ([ph].[LastName] IN ('n/a', 'N/A', ' '))    OR ([ph].[LastName] LIKE '0%')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •