Delete from one table with matching records in another table

[Dale Fye]

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

[Anith Sen]

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 )

[Dean Savovic]

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.com@nomore.dale.fye> wrote in message
news:O$F$VH9PDHA.2480@tk2msftngp13.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]

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.com@nomore.dale.fye> wrote in message
news:O$F$VH9PDHA.2480@tk2msftngp13.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
>
>
>

[Dale Fye]

Works like a charm. Thanks Anith

--
HTH

Dale Fye


"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:#w4hWM9PDHA.704@tk2msftngp13.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 )