SQL Update Union

1

Hi from Germany, can anyone help with this code? I have no idea how to get it work.

UPDATE BESTKK
Set BESTKK.BSSPERR ='WIT'
Where
(
SELECT BESTKK.BSBESTNR as Bezugnahme_oder_Externerefrenz, COUNT(*) as Anzahl_Belege
FROM BESTKK with(Nolock)
where   BESTKK.BSBELTYP  LIKE 'B%' 
and BESTKK.BSMANKEY = 2
AND BESTKK.BSBEAKZ IN(200,502,503,504,506,507,508,509)
and BESTKK.BSBESTNR!=''
and BESTKK.bshinweis not like '%dubletten geprüft%'
and BESTKK.BSDATUM BETWEEN DATEADD(DD, -14, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME)) AND DATEADD(DD, +1, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME))
Group BY BSBESTNR
HAVING COUNT(*) > 1

union all

Select RECHKK.BSBESTNR as Bezugnahme_oder_Externerefrenz, COUNT(*) as Anzahl_Belege
from RECHKK with(Nolock)
Where 
Rechkk.bsbeltyp ='F' AND
RECHKK.bsmankey ='2' AND
RECHKK.BSBESTNR!='' AND
RECHKK.BSBEAKZ IN(200,502,503,504,506,507,508,509) AND
RECHKK.bshinweis not like '%dubletten geprüft%' AND
RECHKK.BSDATUM BETWEEN DATEADD(DD, -14, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME)) AND DATEADD(DD, +1, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME))
Group BY BSBESTNR
HAVING COUNT(*) > 1

union all

SELECT BESTKK.BSEXTREF1 as Bezugnahme_oder_Externerefrenz, COUNT(*) as Anzahl_Belege
FROM BESTKK with(Nolock)
where   BESTKK.BSBELTYP  LIKE 'B%' 
and BESTKK.BSMANKEY = 2
and BESTKK.BSEXTREF1!=''
AND BESTKK.BSBEAKZ IN(200,202,502,503,504,506,507,508,509) 
AND BESTKK.bshinweis not like '%dubletten geprüft%'
and BESTKK.BSDATUM BETWEEN DATEADD(DD, -14, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME)) AND DATEADD(DD, +1, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME))
Group BY BSEXTREF1
HAVING COUNT(*) > 1

union all

Select RECHKK.BSEXTREF1 as Bezugnahme_oder_Externerefrenz, COUNT(*) as Anzahl_Belege
from RECHKK with(Nolock)
Where 
Rechkk.bsbeltyp ='F' AND
RECHKK.bsmankey ='2' AND
RECHKK.BSEXTREF1!='' AND
RECHKK.BSBEAKZ IN(200,202,502,503,504,506,507,508,509) AND
RECHKK.bshinweis not like '%dubletten geprüft%'AND
RECHKK.BSDATUM BETWEEN DATEADD(DD, -14, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME)) AND DATEADD(DD, +1, CAST(CONVERT(VARCHAR, GETDATE(), 112) AS DATETIME))
Group BY BSEXTREF1
HAVING COUNT(*) > 1
Order BY Anzahl_Belege desc 
)
2

Looks like BESTKK is a schema and not a table name, so it doesn't know what to update. The update makes no sense. What are you trying to do? If you can provide DDL and sample data, we should be able to help. Also, order by in the last union all is probably no needed either. Maybe some of the counts can be combined into 1 query? Again, can't tell without DDL. What's the difference between the 1st 2 in the union all and the last 2?