Professional Web Applications Themes

Delete from one table with matching records in another table - Microsoft SQL / MS SQL Server

I'm an Access programmer making the migration to SQL Server, and I want to delete all the records from one table with matching records in another table, but am getting a syntax error: Incorrect syntax near the keyword 'INNER'. The second table starts out as a string of comma separated text variables but gets converted to a table by the charlist_to_table UDF. In Access I'd create a temp table for the Users and do something like the following, but that doesn't work either DELETE ProfileUsers.* FROM ProfileUsers INNER JOIN tempUsers on ProfileUsers.UserID = tempUsers.UserID WHERE ProfileID = 1 Thanks to ...

  1. #1

    Default Delete from one table with matching records in another table

    I'm an Access programmer making the migration to SQL Server, and I
    want to delete all the records from one table with matching records in
    another table, but am getting a syntax error: Incorrect syntax near
    the keyword 'INNER'. The second table starts out as a string of comma
    separated text variables but gets converted to a table by the
    charlist_to_table UDF.

    In Access I'd create a temp table for the Users and do something like
    the following, but that doesn't work either

    DELETE ProfileUsers.*
    FROM ProfileUsers
    INNER JOIN tempUsers
    on ProfileUsers.UserID = tempUsers.UserID
    WHERE ProfileID = 1

    Thanks to Aaron B and Erland S, I now have a UDF that allows me to
    pass a comma delimted string to a function and get a table of values
    back. My T-SQL now looks like:

    CREATE PROCEDURE proc_Exercise_User_Profile_UnAssign

    ProfileID INT,
    Users Varchar(8000)

    AS

    DELETE FROM ProfileUser
    INNER JOIN charlist_to_table(Users, ',') S
    ON ProfileUser.UserID = Users.str
    WHERE ProfileUser.ProfileID = ProfileID

    GO


    Thanks!!!
    --

    Dale Fye



    Dale Fye Guest

  2. #2

    Default Re: Delete from one table with matching records in another table

    In T-SQL, you'll have to use an ackward syntax like:

    DELETE ProfileUser
    FROM ProfileUser
    INNER JOIN charlist_to_table(Users, ',') S
    ON ProfileUser.UserID = Users.str
    WHERE ProfileUser.ProfileID = ProfileID

    A clearer construct would be to use EXISTS/IN clause like:

    DELETE FROM ProfileUser
    WHERE EXISTS ( SELECT *
    FROM charlist_to_table(Users, ',') f1
    WHERE f1.str = ProfileUser.UserID )
    AND ProfileID = ProfileID ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: Delete from one table with matching records in another table

    Try this:

    CREATE PROCEDURE proc_Exercise_User_Profile_UnAssign

    ProfileID INT,
    Users Varchar(8000)

    AS

    DELETE ProfileUser
    FROM ProfileUser
    INNER JOIN charlist_to_table(Users, ',') S
    ON ProfileUser.UserID = Users.str
    WHERE ProfileUser.ProfileID = ProfileID


    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message
    news:O$F$VH9PDHA.2480tk2msftngp13.phx.gbl...
    > I'm an Access programmer making the migration to SQL Server, and I
    > want to delete all the records from one table with matching records in
    > another table, but am getting a syntax error: Incorrect syntax near
    > the keyword 'INNER'. The second table starts out as a string of comma
    > separated text variables but gets converted to a table by the
    > charlist_to_table UDF.
    >
    > In Access I'd create a temp table for the Users and do something like
    > the following, but that doesn't work either
    >
    > DELETE ProfileUsers.*
    > FROM ProfileUsers
    > INNER JOIN tempUsers
    > on ProfileUsers.UserID = tempUsers.UserID
    > WHERE ProfileID = 1
    >
    > Thanks to Aaron B and Erland S, I now have a UDF that allows me to
    > pass a comma delimted string to a function and get a table of values
    > back. My T-SQL now looks like:
    >
    > CREATE PROCEDURE proc_Exercise_User_Profile_UnAssign
    >
    > ProfileID INT,
    > Users Varchar(8000)
    >
    > AS
    >
    > DELETE FROM ProfileUser
    > INNER JOIN charlist_to_table(Users, ',') S
    > ON ProfileUser.UserID = Users.str
    > WHERE ProfileUser.ProfileID = ProfileID
    >
    > GO
    >
    >
    > Thanks!!!
    > --
    >
    > Dale Fye
    >
    >
    >

    Dean Savovic Guest

  4. #4

    Default Re: Delete from one table with matching records in another table

    Dale,

    DELETE ProfileUser -- This is the missing piece. Note that there is no * or
    column list.
    FROM ProfileUser
    INNER JOIN charlist_to_table(Users, ',') S
    ON ProfileUser.UserID = Users.str
    WHERE ProfileUser.ProfileID = ProfileID

    Russell Fields
    "Dale Fye" <spam.saic.comnomore.dale.fye> wrote in message
    news:O$F$VH9PDHA.2480tk2msftngp13.phx.gbl...
    > I'm an Access programmer making the migration to SQL Server, and I
    > want to delete all the records from one table with matching records in
    > another table, but am getting a syntax error: Incorrect syntax near
    > the keyword 'INNER'. The second table starts out as a string of comma
    > separated text variables but gets converted to a table by the
    > charlist_to_table UDF.
    >
    > In Access I'd create a temp table for the Users and do something like
    > the following, but that doesn't work either
    >
    > DELETE ProfileUsers.*
    > FROM ProfileUsers
    > INNER JOIN tempUsers
    > on ProfileUsers.UserID = tempUsers.UserID
    > WHERE ProfileID = 1
    >
    > Thanks to Aaron B and Erland S, I now have a UDF that allows me to
    > pass a comma delimted string to a function and get a table of values
    > back. My T-SQL now looks like:
    >
    > CREATE PROCEDURE proc_Exercise_User_Profile_UnAssign
    >
    > ProfileID INT,
    > Users Varchar(8000)
    >
    > AS
    >
    > DELETE FROM ProfileUser
    > INNER JOIN charlist_to_table(Users, ',') S
    > ON ProfileUser.UserID = Users.str
    > WHERE ProfileUser.ProfileID = ProfileID
    >
    > GO
    >
    >
    > Thanks!!!
    > --
    >
    > Dale Fye
    >
    >
    >

    Russell Fields Guest

  5. #5

    Default Re: Delete from one table with matching records in another table

    Works like a charm. Thanks Anith

    --
    HTH

    Dale Fye


    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:#w4hWM9PDHA.704tk2msftngp13.phx.gbl...
    In T-SQL, you'll have to use an ackward syntax like:

    DELETE ProfileUser
    FROM ProfileUser
    INNER JOIN charlist_to_table(Users, ',') S
    ON ProfileUser.UserID = Users.str
    WHERE ProfileUser.ProfileID = ProfileID

    A clearer construct would be to use EXISTS/IN clause like:

    DELETE FROM ProfileUser
    WHERE EXISTS ( SELECT *
    FROM charlist_to_table(Users, ',') f1
    WHERE f1.str = ProfileUser.UserID )
    AND ProfileID = ProfileID ;

    --
    - Anith
    ( Please reply to newsgroups only )



    Dale Fye Guest

Similar Threads

  1. How to get All Records in Table A that don't havematching ID in Table B
    By Photoshare in forum Coldfusion Database Access
    Replies: 4
    Last Post: February 23rd, 10:46 PM
  2. Delete form - Post data to a table and delete uponsubmit.
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 28th, 01:27 PM
  3. Delete key doesn't delete when datagrid is bound to a disconnected table
    By Fred Zolar in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: April 1st, 07:47 AM
  4. Replies: 2
    Last Post: August 12th, 07:55 AM
  5. Delete the table link but keep the table
    By Maureen in forum Microsoft Access
    Replies: 3
    Last Post: July 21st, 10:27 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139