Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: how can I delete duplicate regists

    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
    ) )
    )

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Jun 2008
    Posts
    3
    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;

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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);

  5. #5
    Join Date
    Jun 2008
    Posts
    3
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •