Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator

Ask Question select emp_num, [from_date],[to_date],[ req_ser], [ req_year] from empmission group by emp_num, [from_date],[to_date],[ req_ser], [ req_year] having count(*) >2 DELETE FROM cte

but i get the following exception :

Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

select emp_num, [from_date],[to_date],[ req_ser], [ req_year] from empmission group by emp_num, [from_date],[to_date],[ req_ser], [ req_year] having count(*) >2 DELETE E FROM cte C JOIN empmission E ON C.emp_num = E.emp_num AND C.from_date = E.from_date AND C.to_date = E.to_date AND C.req_ser = E.req_ser AND C.req_year = E.req_year I think you may need to key off of all columns in the select not just emp_num but I'm not certain without table structure. – xQbert Jul 28, 2016 at 13:00

WITH todelete As (
      select em.*,
             row_number() over (partition by emp_num, [from_date],[to_date],[ req_ser], [ req_year]
                                order by (select null)) as cnt
      from empmission em
DELETE FROM todelete
WHERE cnt > 2;
Note that this deletes all rows with duplicate values.  Often, you want to keep one of the values.  If that is the case, ask another question.
                @AnynameDonotcare . . . The row_number() an take advantage of an index on empmission(emp_num, [from_date],[to_date],[ req_ser], [ req_year]).
– Gordon Linoff
                Aug 1, 2016 at 1:19
              where e1.emp_num = e1.emp_num
                and e1.[from_date] = e2.[from_date]
                and e1.[to_date] = e2.[to_date]
                and e1.[ req_ser] = e2.[ req_ser]
                and e1.[ req_year] = e2.[ req_year]
              group by emp_num, [from_date],[to_date],[ req_ser], [ req_year]
              having count(*) > 2)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.