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

Deleting Duplicate Records

I have a table tmPunchtimeSummary which contains a sum of employee's hours
per day. The table contains some duplicates.

code:

CREATE TABLE [tmPunchtimeSummary]
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL
) ON [PRIMARY]
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5

How can I write a delete statement to only delete the duplicates which in
this case would be the 1st and 5th records?
Thanks,
Ninel
Message posted via http://www.webservertalk.comDELETE FROM tmPunchtimeSummary
WHERE EXISTS
(select * from tmPunchtimeSummary as X
where tmPunchtimeSummary.sCalldate = X.sCalldate
and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
and tmPunchtimeSummary.iTmPunchTimeSummaryId <
X.iTmPunchTimeSummaryId)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 16:41:01 GMT, "ngorbunov via webservertalk.com"
<
u9125@.uwe>
wrote:

>
I have a table tmPunchtimeSummary which contains a sum of employee's hours
>
per day. The table contains some duplicates.
>
>

code:

>
CREATE TABLE [tmPunchtimeSummary]
>
(
>
 [iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
>
 [sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>
 [sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>
 [dTotalHrs] [decimal](18, 4) NULL
>
 ) ON [PRIMARY]
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1234', 4.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1234', 4.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2468', 8.0)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1357', 9.0)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2345', 8.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2345', 8.5
>

>
>
How can I write a delete statement to only delete the duplicates which in
>
this case would be the 1st and 5th records?
>
>
Thanks,
>
Ninel|||Thank you very much.
Roy Harvey wrote:
>DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
> (select * from tmPunchtimeSummary as X
> where tmPunchtimeSummary.sCalldate = X.sCalldate
> and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
> and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
> and tmPunchtimeSummary.iTmPunchTimeSummaryId <
> X.iTmPunchTimeSummaryId)
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 32 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
In my figuring on paper, Roy's solution works.
I also offer another suggetion: Microsoft Access has lots and lots of
wizards to do hard stuff like that. So if you have a copy of Access, let it
be your quick-and-dirty friend. You can use the Access wizard to buzz up
some stuff really fast, and then just paste the SQL code into QA, clean it
up a bit, and then run it.
But I will warn you: Access is not up to the task of production code.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

DELETE TOP

Environment: SQL Server 2005
Language: T-SQL

I want to insert the top first record from the temp table '#NewTemp'
to the Employee table 1 record at a time until all the records from the temp table
have been copied.
After inserting the top first record into the Employee table, how can I retrive the identity key 'NewEmployeeID' from the Employee table?
I would then like to delete the top row in the #NewTemp table.
The process should be repeated until the @.Counter variable is 0.
Currently, I get an error msg 'Incorrect syntax near the keyword TOP'.What's wrong with the following T-SQL and how can I fix it? thanks.

BEGIN
DECLARE @.Counter as INT
SELECT @.Counter = COUNT(*) FROM #NewTemp
DECLARE @.TopCount AS INT
SET @.TopCount = 1

WHILE @.Counter > 0
BEGIN
INSERT INTO dbo.Employee(
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail)
SELECT TOP(@.TopCount)
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail
FROM dbo.#NewTemp

SELECT SCOPE_IDENTITY() AS NewEmployeeID

PRINT 'The count is ' + CONVERT (VARCHAR (10), @.Counter)

PRINT --NewEmployeeID

SET @.Counter = @.Counter -1

DELETE TOP(@.TopCount) from dbo.#NewTemp
END

DROP TABLE dbo.#NewTemp
END

I'm sure you have your reasons, but I have to say, this is very, very 'odd' indeed.

DELETE does NOT recognize TOP. You cannot [ DELETE TOP ]. In fact, if users are active in your database, a second query using TOP may not return the same row(s) as the first query. In your SELECT query, TOP without a WHERE criteria just doesn't make any sense.

Why go to so much trouble?

Let us know what you are attempting to accomplish and perhaps we can help you.

|||

What I'm trying to accomplish is this.

Copy each row from temp table to the Employee table. After inserting each record into the Employee table,

I would like to get the Identity key for the new record inserted from the Employee table. Thanks.

|||

You will need to use the new OUTPUT clause of the input statement. You cannot use either @.@.identity or scope_identity() when you are inserting multiple rows. Just a second and I will try to get you an example. Give a look to this article:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You ought to be able to do something like this to retrieve your list of identities:

create table #identityList (idKey integer)

insert into targetTable
select col1,
col2,
...
colN
output inserted.theIdentityColumn
into #identityList
from yourTempTable

-- to retrieve the identity values:
select idKey

from #identityList

Now, if you are going to need to match your identies back to the temp table that you inserted from you will also need to include column(s) in your identity list that make the inserted row unique.

|||

thanks, nice article. I think it will help. Is 'inserted' a reserved word in the code above?

In my code above, reason I have while loop and a counter is so I can go each record at time for insertion from the

temp table to the Employee table. What would be the alternative if using scope_identity() or @.@.identity?

|||

John,

In my earlier response, I asked if you would give us more detail information about what you are attempting to accomplish. It is not at all clear what is going on here.

If you only wanted to move the data from the #NewTemp table into the Employees table, it is as simple as an INSERT... SELECT.

Code Snippet

INSERT INTO dbo.Employee
( EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
)
SELECT
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
FROM dbo.#NewTemp

However, perhaps there is some thing you still haven't told us. What is your concern about the SCOPE_IDENTITY() or @.@.IDENTITY?

We can't do a good job of helping you unless you give us all of the information.

|||

create table dbo.#newTemp
( EmployeeFName varchar(20),
EmployeeLName varchar(20),
EmployeePhone varchar(14),
EmployeeEmail varchar(30)
)
insert into dbo.#newTemp
select 'Dave', 'Mugambo', '(800)555-1212', null union all
select 'Kent', 'Waldrop', '(800)555-1212', null union all
select 'Geoff', 'Waldmeyer', '(800)555-1212', null
select * from dbo.#newTemp
/*
EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- --
Dave Mugambo (800)555-1212 NULL
Kent Waldrop (800)555-1212 NULL
Geoff Waldmeyer (800)555-1212 NULL
*/

declare @.idList table (newEmployeeID int)

insert into dbo.Employee
output inserted.employeeId
into @.idList
select EmployeeFName,
EmployeeLname,
EmployeePhone,
EmployeeEmail
from dbo.#newTemp

-- --
-- If you need to see the new employee
-- information, you can you the
-- permanent table to the IDList like
-- this:
-- --

select EmployeeId,
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
from dbo.employee a
inner join @.idList b
on a.employeeId = b.newEmployeeId

/*
EmployeeId EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- -
1 Dave Mugambo (800)555-1212 NULL
2 Kent Waldrop (800)555-1212 NULL
3 Geoff Waldmeyer (800)555-1212 NULL
*/

go

drop table dbo.#newTemp

(edited the syntax; it was previously wrong)

|||

After filling the table variable with the inserted records,

how can I get each newEmployeeId one at a time. I would like to

pass this newEmployeeID to another sp one at a time.

thanks much.

|||

If this is what you must do, then use a cursor with a while loop -- but understand, cursors tend to be performance killers. A better approach is to design set based operations; it is what SQL server is best at. I will edit this with some posts that talk about the problems of cursors.

Here are some posts that comment on cursors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

I have many more examples in my list.

|||

If you really MUST do a row-wise operation, instead of a CURSOR, I recommend using the previous suggestion of the SQL 2005 OUTPUT keyword, outputing into a table variable, then adding an IDENTITY column to the table variable, and then using a WHILE loop to step through the rows in the table variable.

It will not be as resource intensive on the server, and most likely even execute faster than a CURSOR.

|||

As an aside, delete does recognize TOP in 2005. It is non ordered, so it doesn't make sense in this particular case. It's use is to batch deletes, like if you want to delete a large number of rows, but want to limit the pressure on the log, you can just delete them in batches over and over until all data is gone.

Code Snippet

create table #NewTemp
(
EmployeeFName varchar(30)
, EmployeeLName varchar(30)
, EmployeePhone varchar(30)
, EmployeeEmail varchar(30)
)

insert into #newTemp
select 'fred','flintstone','quarry 3424','fredf@.slate.com'
union
select 'barney','rubble','quarry 3452','barneyr@.slate.com'

delete top (1) from #newTemp

select *
from #newTemp