Deleting records.

Anyone have ideas on how to delete records (see code below). I keep on getting the error message:

"The column prefix 'employee' does not match with a table name or alias name used in the query."

All I want to do is to remove the records in the EMPRATES table where the EMPLOYEEID and RATE are the same in the EMPLOYEE table. What am I missing?

delete emprates
where
emprates.employeeid = employee.employeeid
and emprates.rate=employee.ratedelete emprates from emprates
inner join employee on
emprates.employeeid = employee.employeeid
and emprates.rate=employee.rate|||delete from emprates
where exists
( select 1 from employee
where employeeid = emprates.employeeid
and rate = emprates.rate )|||I tried both syntax and they both perfomed what I needed. The first one had a lower execution cost though.

Thanks again.sql

deleteing small amount of records from a view causes IX lock on all the base tables.

Sorry that I had to post it as new message instead of replying since I got server application error.
Kalen, this is a different issue. I wonder why other 4 base tables got IX TAB lock as well since the partitioned view is supposed to look up the relevant tables only by querying on the constraint column.Tom
Can you please include relevant portions of the original message, so I can
know what I am replying to without having to search the archives?
If this is a question about partitioned views, did you supply the view
definition, and the version you are using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:29346002-6963-4D4E-B63C-C6A5C5E292CD@.microsoft.com...
> Sorry that I had to post it as new message instead of replying since I got
server application error.
> Kalen, this is a different issue. I wonder why other 4 base tables got IX
TAB lock as well since the partitioned view is supposed to look up the
relevant tables only by querying on the constraint column.

Delete Statement

Hi,

I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement:

DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN (SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2))

The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?

Thanks.

Eitan

Try this. Not sure if i got your logic correct.

DELETE DFROM id_email_message DINNERJOIN recipients RON D.id_message = R.id_messageINNERJOIN message MON D.id_message = M.id_messageWHERE T.id_user = 324AND M.id_message_status = 2AND M.id_owner = 324

delete query with inner join

Hallo,
Can anybody let me know what I need to change to make this query work:
delete
from a
inner b
on a.item = b.item
I get the error message "Incorrect syntax near the keyword 'inner'."
Thanks,
Stephen.Hallo,

Can anybody let me know what I need to change to make this query work:

delete
from a
inner b
on a.item = b.item

I get the error message "Incorrect syntax near the keyword 'inner'."

Thanks,
Stephen.

Have you tried?

INNER JOIN b on...|||Hi Steve o

Key word is join rather than inner. Also, you'll need to use an EXISTS or IN statement rather than a join if you are deleting

HTH|||Check BOL for delete statment,
Here is the corect syntax,

delete a
from a
inner join b
on a.item = b.item

Delete problem-too many parameters

I have a dataview control with the delete method pointing to a logical delete stored procedure in SQL SERVER Express. I am getting an error message saying too many parameters provided. I've check and there is one parameter expected and one passed in. This is my SP, the html, and the debug infor I'm looking at. Any ideas?

PROCEDUREdbo.usp_Drivers_Delete

@.mintDriver_IDint

AS

UPDATEtblDrivers

SETActive= 0

WHEREDriver_ID=@.mintDriver_ID

html:

<DeleteParameters>

<asp:ControlParameterControlID="GridView1"Name="mintDriver_ID"PropertyName="SelectedValue"

Type="Int32"/>

</DeleteParameters>

Debug:

?SqlDataSourceDrivers.DeleteParameters(0)

{System.Web.UI.WebControls.ControlParameter}

System.Web.UI.WebControls.ControlParameter: {System.Web.UI.WebControls.ControlParameter}

ConvertEmptyStringToNull: True

DefaultValue: Nothing

Direction: Input {1}

Name: "mintDriver_ID"

Size: 0

Type: Int32 {9}

?SqlDataSourceDrivers.DeleteParameters.Count

1

Error:

Exception Details:System.Data.SqlClient.SqlException: Procedure or function usp_Drivers_Delete has too many arguments specified.

Perhaps I'm missing something, but you don't really have a delete. You have an update that sets a status column (Active) to 0. I think if you try changing the <DeleteParameters> to <UpdateParameters> you should be fine.

Deb