-2

Below is my query -

DELETE
FROM activity_details as ad, edit_details as ed
LEFT JOIN edit_details as ed
ON ed.details_id = ad.row_id
WHERE year(ad.activity_datetime)  = year(GETDATE())-10

I am getting error SQL Error [156] [S1000]: Incorrect syntax near the keyword 'as'. SQL Error [156] [S1000]: Incorrect syntax near the keyword 'as'.

Can you please help me correct the syntax

shreya
  • 95
  • 3
  • 11
  • 1
    For any new visitors, I suggest reading the comments under the answers; the question the OP is asking above is not their "true" question. – Thom A Feb 25 '19 at 09:36
  • Possible duplicate of [How do I delete from multiple tables using INNER JOIN in SQL server](https://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server) – SMor Feb 25 '19 at 12:55

4 Answers4

3

Change it like following. You need to delete with alias name if you are using FROM in your delete statement.

DELETE ad
FROM activity_details as ad
LEFT JOIN edit_details as ed
ON ed.details_id = ad.row_id
WHERE year(ad.activity_datetime)  = year(GETDATE())-10
PSK
  • 17,547
  • 5
  • 32
  • 43
  • I need to delete row from ed also, where rowid of ad is activityid of ed – shreya Feb 25 '19 at 09:29
  • SQL Server allows only one table delete in one statement, you can't delete rows from multiple tables using a single statement, if you want to delete multiple, you can store the data in temp table and execute two seperate delete statements . If you want you can wrap those statements inside a transaction. – PSK Feb 25 '19 at 09:30
  • so how do we handle foreign key constraints.. because edit detaills has a foreign key which references rowid – shreya Feb 25 '19 at 09:34
  • If you want to do CASCADE DELETE, it should be defined in the FK, you can check more details here https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server – PSK Feb 25 '19 at 09:36
1

Add alias name after delete 

 DELETE ad
    FROM activity_details as ad
    LEFT JOIN edit_details as ed
    ON ed.details_id = ad.row_id
    WHERE year(ad.activity_datetime)  = year(GETDATE())-10
Chanukya
  • 5,833
  • 1
  • 22
  • 36
1

You need to use the alias in the delete clause, and then have the FROM. I also suggest against year(ad.activity_datetime) and use a SARGable query:

DELETE ad
FROM activity_details ad
     LEFT JOIN edit_details ed ON ed.details_id = ad.row_id
WHERE ad.activity_datetime >= DATEADD(YEAR,DATEDIFF(YEAR, 0, GETDATE()-10),0)
  AND ad.activity_datetime < DATEADD(YEAR,DATEDIFF(YEAR, 0, GETDATE()-9),0);
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • I need to delete from ed also based on ed.details_id = ad.row_id – shreya Feb 25 '19 at 09:26
  • @shreya SQL Server doesn't affect multiple tables with a single DDL statement. YOu will need to issue 2 `DELETE` statements or implement cascading. But why were those details in your question? – Thom A Feb 25 '19 at 09:30
  • so there is another table edit_details whose foreign key is activity id and references rowid – shreya Feb 25 '19 at 09:32
  • @shreya that doesn't change my above comment. SQL Server still won't allow you affect multiple tables with a single `DELETE`. – Thom A Feb 25 '19 at 09:33
  • so how do we handle foreign key deletion – shreya Feb 25 '19 at 09:35
  • You can implement cascading, or you'll need to run a `DELETE` on the other table first. – Thom A Feb 25 '19 at 09:35
0

You need to remove aliases from the your query.

snnpro
  • 193
  • 2