Sql procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql procedure

Author

Topic

gopieee16
Starting Member

5 Posts

Posted - 2011-06-15 : 06:29:53

Hi all,
I'm writing the following procedure to delete the duplicate records from employees table.

Employees table contains following data :

empid first_name last_name sal identitycolumn
7 gopi kishan 656565 13
8 rachel andy 25463 14
9 fun green 5689 15
10 zolskty kia 1656565 16
11 james karen 56565 17
12 santo sandy 6565 18
7 gopi kishan 656565 7
8 rachel andy 25463 8
9 fun green 5689 9
10 zolskty kia 1656565 10
11 james karen 56565 11
12 santo sandy 6565 12

my logic is
delete from employees where identitycolumn <> (select max(q) from employees b
where b.first_name = employees.first_name

and my procedure is

create procedure usp_testparse
@p_column varchar(23)

as

begin

DELETE FROM employees WHERE identitycolumn <> (SELECT max(identitycolumn) FROM employees b WHERE employees.@p_column = b.@p_column)

end

if is run this i get the following error :

Msg 102, Level 15, State 1, Procedure usp_testparse, Line 8
Incorrect syntax near '@p_column'.

Please help me in fix this, Thanks in advance.

Regards
gopi

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-15 : 06:42:33

That won't delete duplicates. It'll delete most of the table (if it worked).
don't run this but
DELETE FROM employees WHERE identitycolumn <> (SELECT max(identitycolumn) FROM employees b WHERE b.first_name = @p_column)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-15 : 06:45:25

something more like this?
delete tbl
from tbl t
join (select firstname, lastname, id = MAX(id) from tbl group by firstname, lastname having COUNT(*) > 1) t2
on t2.firstname = t.firstname
and t2.lastname = t.lastname
and t2.id <> t.id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gopieee16
Starting Member

5 Posts

Posted - 2011-06-15 : 06:47:32

The logic which i mentioned above is working and it will delete the duplicate rows (for example in the above table which i mentioned there are 12 rows with six duplicates and it will delete all the six rows thats wat i want)

If i use "DELETE FROM employees WHERE identitycolumn <> (SELECT max(identitycolumn) FROM employees b WHERE b.first_name = @p_column)"
it will delete only one row so i have to run this procedure/query nearly 6 times to delete the full duplicates records.

gopieee16
Starting Member

5 Posts

Posted - 2011-06-15 : 06:52:29

thanks for your logic to delete the duplicate rows right nw i want to know why i'm getting error when creating procedure please look into it
thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-15 : 06:56:12

Lookk at the delete statement I posted. You had tbl.@variable which is invalid. It's tbl.column or @variable.
Note - that query won't do what you expect it t9o

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gopieee16
Starting Member

5 Posts

Posted - 2011-06-15 : 07:04:22

I didnt get u can u explain "You had tbl.@variable which is invalid. It's tbl.column or @variable."

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-15 : 07:11:38

b.@p_column
b is an alias for the table and this needs to reference a column in that table
I see what you are tryingb to do - maybe

DELETE employees
from employees e
WHERE e.identitycolumn <> (SELECT max(e2.identitycolumn) FROM employees e2 WHERE e2.firstname = e.firstname)
and e.firstname = @p_column

You can drop the
and e.firstname = @p_column

and it will do the whole table.
You might want to nclude lastname as well though

DELETE employees
from employees e
WHERE e.identitycolumn <> (SELECT max(e2.identitycolumn) FROM employees e2 WHERE e2.firstname = e.firstname and e2.lastname = e.lastname)

I would go with my previous statement though as it only checks duplicates for the delete and gives a subquery that you can run to see what will be affected.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gopieee16
Starting Member

5 Posts

Posted - 2011-06-15 : 07:28:24

Thanks for your clarification,

I'll go with "DELETE employees
from employees e
WHERE e.identitycolumn <> (SELECT max(e2.identitycolumn) FROM employees e2 WHERE e2.firstname = e.firstname and e2.lastname = e.lastname)"

Because i already created a procedure in Oracle with same logic which we are using now, and more over in a production database with more number of records it is very difficult to delete the duplicate records one by one right?

The procedure which i created in oracle with 2nd logic is :

create or replace procedure test_parse (p_table varchar2, pvalue varchar2)
is
den varchar2(100);
v_count number(6) := length(regexp_replace(pvalue, '[[:alpha:]]', '')) - 1;
query varchar2(20000);
begin
query:= 'DELETE FROM ' || p_table || chr(10) || ' WHERE ROWID <> ' || chr(10)
|| '( SELECT Max(ROWID) FROM '
|| p_table || ' b ' || ' where b.';

for i in 1..v_count loop
den := substr(pvalue,
((instr (pvalue, ',',1,i)+1)),
(((instr (pvalue, ',',1,i+1))) - (((instr (pvalue, ',',1,i)+1))))
);

query := query || den || ' = ' || p_table || '.'|| den || chr(10) || ' and b.';
end loop;

query := substr(query, 1, length(query) - 7) || ')';

EXECUTE IMMEDIATE query;

end test_parse;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-15 : 07:45:18

You can run a similar query in oracle to that which I first posted.
No need for a loop or dynamic sql.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources