zimmer9
asked on
Do you know why I get the syntax error Msg 102, Level 15 while using SQL Server 2005?
I am developing an Access application using Access 2003 with an ADP type file.
Do you now why the following statement causes a syntax error using SQL Server 2005?
The syntax error is as follows:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'A'
delete
from dbo.tblGetDocumentAttribut es A
where exists (select 1 from dbo.tblGetDocumentAttribut es B
where B.Account = A.Account
and B.DocumentDate = A.DocumentDate
and B.MailID < A.MailID)
-------------------------- ---------- ---------- ---------- ---------- ----------
MailID Account DocumentDate ScanDate Amount.
1 A34R 45677 2010-08-31 00:00:00.000
2 B78E 45677 2010-09-30 00:00:00.000
3 R546 45677 2011-01-31 00:00:00.000
4 G555 45677 2011-01-31 00:00:00.000
5 C645 86453 2010-08-31 00:00:00.000
6 E567 86453 2010-09-30 00:00:00.000
7 R546 86543 2011-01-31 00:00:00.000
8 R555 86543 2011-02-28 00:00:00.000
My goal was to delete any records from this table in which
any particular account has multiple records with the same documentdate and in these instances just keep the record with the higher MailID (type nvarchar) ?
So in the example above, the 4th record would be deleted because it has the same
account plus documentdate as record 3 but record 4 has the lower ranking MailID.
Do you now why the following statement causes a syntax error using SQL Server 2005?
The syntax error is as follows:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'A'
delete
from dbo.tblGetDocumentAttribut
where exists (select 1 from dbo.tblGetDocumentAttribut
where B.Account = A.Account
and B.DocumentDate = A.DocumentDate
and B.MailID < A.MailID)
--------------------------
MailID Account DocumentDate ScanDate Amount.
1 A34R 45677 2010-08-31 00:00:00.000
2 B78E 45677 2010-09-30 00:00:00.000
3 R546 45677 2011-01-31 00:00:00.000
4 G555 45677 2011-01-31 00:00:00.000
5 C645 86453 2010-08-31 00:00:00.000
6 E567 86453 2010-09-30 00:00:00.000
7 R546 86543 2011-01-31 00:00:00.000
8 R555 86543 2011-02-28 00:00:00.000
My goal was to delete any records from this table in which
any particular account has multiple records with the same documentdate and in these instances just keep the record with the higher MailID (type nvarchar) ?
So in the example above, the 4th record would be deleted because it has the same
account plus documentdate as record 3 but record 4 has the lower ranking MailID.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.