I'm working with a legacy application that stores its data in a SQL Server 2
K
database. I want to write a delete query to delete items from table A, wher
e
there is no match in table B, where the match is based on matches of the two
PK fields.
I tried the following, but got an error: Incorrect syntax near the keyword
'LEFT'
DELETE FROM A
LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
Any help would be greatly appreciated.
DaleHow about this?
DELETE A
FROM A
INNER JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL|||Try
DELETE A
From A LEFT JOIN B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
the Tables are actually name dTableA and TableB, then the First line has t
ouse the Alias, not the actual Table Name,. i.e.,
DELETE A
From TableA A Left Join TableB B
ON A.ExerciseID = B.ExerciseID
AND A.UserID = B.UserID
WHERE B.UserID IS NULL
A Much clearer way to code this is to use SQL that mirrors exactly what you
want
Delete TableA
Where Not Exists
(Select * From TableB
Where PK = TableA.PK)
"Dale Fye" wrote:
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||I meant
Delete TableA
Where Not Exists
(Select * From TableB
Where ExerciseID = TableA.ExerciseID
And UserID = TableA.UserID)
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>|||Try this
Delete From A
Where Not Exists(
Select * From B Where B.Col1=A.Col1 And B.Col2=A.Col2
)
Dmitriy
"Dale Fye" <dale.fye@.nospam.com> wrote in message
news:B16B3312-7EDD-4D85-A2D0-7806F2064BEE@.microsoft.com...
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A,
where
> there is no match in table B, where the match is based on matches of the
two
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> Any help would be greatly appreciated.
> Dale|||Dale Fye wrote:
> I'm working with a legacy application that stores its data in a SQL Server
2K
> database. I want to write a delete query to delete items from table A, wh
ere
> there is no match in table B, where the match is based on matches of the t
wo
> PK fields.
> I tried the following, but got an error: Incorrect syntax near the keyword
> 'LEFT'
> DELETE FROM A
> LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
DELETE FROM A
WHERE NOT EXISTS (SELECT * FROM B
WHERE B.ExerciseID = A.ExerciseID
AND B.UserID = A.UserID)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjX6lIechKqOuFEgEQIHQQCfe67DnoBuMAKw
lKtCX8+H7Wd9ANAAmwS2
sbyoMnwLgSk2DmyMUtxtgESf
=hYSb
--END PGP SIGNATURE--|||Thanks to all who responded. I've been working in Access so long, I forgot
about Exists and Not Exists.
"CBretana" wrote:
> Try
> DELETE A
> From A LEFT JOIN B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> But beware, when you use this syntax, if you uswe Table Aliases, i.e., if
> the Tables are actually name dTableA and TableB, then the First line has t
> ouse the Alias, not the actual Table Name,. i.e.,
> DELETE A
> From TableA A Left Join TableB B
> ON A.ExerciseID = B.ExerciseID
> AND A.UserID = B.UserID
> WHERE B.UserID IS NULL
> A Much clearer way to code this is to use SQL that mirrors exactly what yo
u
> want
> Delete TableA
> Where Not Exists
> (Select * From TableB
> Where PK = TableA.PK)
> "Dale Fye" wrote:
>
