Delete with sub-query IN SQL

Asked

Viewed 165 times

1

good morning friends !

I am unable to delete with Count SQL server someone could give a force?

Message 116, Level 16, Status 1, Line 32 Only an expression can be specified in the selection list when the subconsultation is not entered with EXISTS.

DELETE FROM PROGRAMAS
    WHERE ID_GERAL IN
       (SELECT ID_GERAL, COUNT(ID_GERAL) AS TOTAL FROM PROGRAMAS
        WHERE   
        Programa LIKE '%BEM ESTAR%' AND 
        Programa NOT LIKE 'Bem Estar - Gilenya' AND 
        Programa NOT LIKE 'Bem Estar Melanoma' AND
        Programa NOT LIKE 'Bem Estar - ILARIS' 

        GROUP BY ID_GERAL
        HAVING COUNT(ID_GERAL) > 1)
sql sql-server

1 answer

1


In your subselect, you should remove TOTAL, your query should look like this:

DELETE FROM PROGRAMAS
WHERE ID_GERAL IN
   (SELECT ID_GERAL FROM PROGRAMAS
    WHERE   
    Programa LIKE '%BEM ESTAR%' AND 
    Programa NOT LIKE 'Bem Estar - Gilenya' AND 
    Programa NOT LIKE 'Bem Estar Melanoma' AND
    Programa NOT LIKE 'Bem Estar - ILARIS' 

    GROUP BY ID_GERAL
    HAVING COUNT(ID_GERAL) > 1)

  • worked well, vlw, but I doubt.. if I want to leave a single record of duplicates example: has 4 duplicates I want to delete 3 and leave 1

  • @MAGUIM although it has nothing to do with your question, take a look at the following link, referring to improving the "performance of a query with like '%String%' changing the Collation", since in your query you are using: hyperlinks

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.