how can I delete duplicate regists

pfonseka · #1 (**permalink**) 06-12-08, 11:00

i'm trying to delete duplicate regists with this code, but I'vve got this error:
"You can't specify target table 'newsletters_queu' for update in FROM clause "

the code retrives the data I want to delete but doesn't allow me to do it.

DELETE FROM newsletters_queu WHERE (
( (
newsletters_queu.id
) NOT IN (
SELECT min( newsletters_queu.id )
FROM newsletters_queu
GROUP BY newsletters_queu.idnewsletter, newsletters_queu.idendereco
) )
)

mike_bike_kite · #2 (**permalink**) 06-12-08, 11:15

You could either put the value into a variable if you're using a stored proc

Code:

declare v_id int;

SELECT min( newsletters_queu.id ) 
into v_id
FROM newsletters_queu;

DELETE FROM newsletters_queu 
WHERE newsletters_queu.id != v_id;

or into a temporary table a bit like this

Code:

SELECT min( newsletters_queu.id ) as id
into tmp_tab
FROM newsletters_queu;

DELETE FROM newsletters_queu 
WHERE newsletters_queu.id not in ( select id from tmp_tab);

drop table tmp_tab;

Mike

pfonseka · #3 (**permalink**) 06-12-08, 12:06

I'm getting this error : "Undeclared variable: tmp_tab"

when I execute:

SELECT min( newsletters_queu.id ) as id
into tmp_tab
FROM newsletters_queu;

mike_bike_kite · #4 (**permalink**) 06-12-08, 12:19

Sorry - I've been using Sybase all day!

Code:

-- do this once
create table tmp_id (
   id   int
);

-- then do this each time you want to do the delete
delete from tmp_id;

insert tmp_id
SELECT min( id )
FROM newsletters_queu;

DELETE FROM newsletters_queu 
WHERE id not in ( select id from tmp_tab);

pfonseka · #5 (**permalink**) 06-12-08, 12:53

thanks you for your help... its working:

CREATE TABLE TMP_id(id int);

INSERT TMP_id SELECT min( newsletters_queu.id ) FROM newsletters_queu GROUP BY newsletters_queu.idnewsletter, newsletters_queu.idendereco;

DELETE FROM newsletters_queu WHERE id not in ( select id from TMP_id)

DROP TABLE TMP_id;

Internet Services

Web Development

Digital Marketing

Consumer Tech