CODEWITHSUNDEEP
sql
apkos99
apkos99

Reputation: 109

sql query for delete

delete  a.Employee_LastName, a.Employee_Firstname 
FROM    EMPLOYEE a JOIN DEPARTMENT b on a.Department_ID = b.Department_ID 
where b.Department_Name='Sales' 
Group by a.Employee_LastName, a.Employee_Firstname, b.Department_Name

can you explain what i am doing wrong? error(in sql server 2008):Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.

Upvotes: 2

Views: 177

Answers (6)

Divas
Divas

Reputation: 453

basic syntax is delete from where condition

from table_name is where u can create any sub query also as per your requirement.

Upvotes: 0

Yogendra Singh
Yogendra Singh

Reputation: 34367

I believe you are trying to delete the values from the columns which is an update not delete. use the update query as below:

    update a
    set a.Employee_LastName = null,
          a.Employee_Firstname = null
    from EMPLOYEE a INNER JOIN DEPARTMENT b
    ON a.Department_ID = b.Department_ID
    where b.Department_Name='Sales'

This query will remove the values (set to null) from Employee_Firstname and Employee_LastName columns for all employees in Sales department.

Upvotes: 0

Bishnu Paudel
Bishnu Paudel

Reputation: 2079

"Delete" should be followed by rows, not individual columns. You should do:

delete a 
FROM    EMPLOYEE a JOIN DEPARTMENT b on a.Department_ID = b.Department_ID 
where b.Department_Name='Sales' 
Group by a.Employee_LastName, a.Employee_Firstname, b.Department_Name

Upvotes: 1

RAY
RAY

Reputation: 474

DELETE works on a row level, so SQL Server is complaining that you have specified columns.

Here's the syntax page for DELETE: http://msdn.microsoft.com/en-us/library/ms189835.aspx

If you're just trying to clear the values in those columns, you should use an UPDATE statement: http://msdn.microsoft.com/en-us/library/aa260662(v=sql.80).aspx

If you do want to delete the rows based on that JOIN and WHERE clause, you will need to write a DELETE statement with a subquery to retrieve all the rows you want.

Upvotes: 0

Mike D
Mike D

Reputation: 196

You cannot specify columns in a delete statement. It should be DELETE FROM <tablename>. Also, if you want to delete using a Join, you need to specify which table you are deleting from. So, something like:

DELETE FROM a FROM <tablename> a JOIN <othertable> b ON a.<col> = b.<col>

Upvotes: 1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195972

Delete is for deleting whole rows.. not emptying fields (so column names are not supported, also no grouping)

so

DELETE EMPLOYEE
FROM   EMPLOYEE a JOIN DEPARTMENT b ON a.Department_ID = b.Department_ID 
WHERE  b.Department_Name = 'Sales'

If you want to edit the records then us UPDATE

Upvotes: 3

Related Questions