SQL Delete

Transact-SQL
1

Hi,
I want to delete the mitarbeiter=employee having the minimum count of kunde= customers
Same statement with SELECT he returns all empleyees not just one with the lowest count.
What is wrong about the statement?

DELETE *
FROM mitarbeiter
GROUP BY mitarbeiter.pers_nr
HAVING MIN(
(SELECT count(*) AS 'A'
FROM mitarbeiter
LEFT JOIN kunde ON mitarbeiter.pers_nr=kunde.betreuer
GROUP BY mitarbeiter.pers_nr))

2

Something like this perhaps:

--delete a
select *
  from mitarbeiter as a
 where a.pers_nr=(select top(1)
                         b.pers_nr
                    from mitarbeiter as b
                         left outer join kunde as c
                                      on c.betreuer=b.pers_nr
                   order by count(*) over(partition by b.pers_nr)
                 )
;
1 Like
3

Unfortuenately we just have a sqlite database but we are allowed to use sql syntax
I cant check it because sqlite doesnt seem to know the top() and over() function xD

4

I'm used to work with Microsoft SQL Server, so I'm not sure if this will work (but it should be close to generic sql):

--delete
select *
  from mitarbeiter as a
 where a.pers_nr=(select a.pers_nr
                    from mitarbeiter as b
                         left outer join kunde as c
                                      on c.betreuer=b.pers_nr
                   group by b.pers_nr
                   limit 1
                 )
;
1 Like
5

Returns the same what my 1st forumstatement returns

6

Which is?

7

All rows grouped by pers_Nr

8

Hmm, I made a mistake on the subselect. Try this:

--delete
select *
  from mitarbeiter as a
 where a.pers_nr=(select b.pers_nr
                    from mitarbeiter as b
                         left outer join kunde as c
                                      on c.betreuer=b.pers_nr
                   group by b.pers_nr
                   limit 1
                 )
;
1 Like
9

It helped a lot to find the right solution.
But theres still a problem when i change SELECT to DELETE:

DELETE
FROM mitarbeiter
WHERE mitarbeiter.pers_nr=
(SELECT mitarbeiter.pers_nr
FROM mitarbeiter
LEFT JOIN kunde ON kunde.betreuer=mitarbeiter.pers_nr
group by mitarbeiter.pers_nr
order by count(*)
limit 1);

It says: " Table 'mitarbeiter' is specified twice, both as a target for 'DELETE' and as a separate source for data"

How can I change that?

10

I have absolutely no experience with SQLite, so I'm guessing here:

--delete a
select a.pers_nr
  from mitarbeiter as a
       left outer join kunde as b
                    on b.betreuer=a.pers_nr
 group by b.pers_nr
 order by count(*)
 limit 1
;

I found what "#define SQLITE_ENABLE_UPDATE_DELETE_LIMIT" has to be compiled into your db engine for this to work, but again - I have absolutely no experience with SQLite.

1 Like