UPDATE Prefar P Join
(select prefarid, AcquisitionCost, assetid from PreFAR p1 where p1.prefarid in
(select distinct p2.prefarid from PreFAR p2 INNER JOIN
(select tbl.prefarid, max(tbl.AcquisitionCost) as AcquisitionCost, tbl.assetid from
(select prefarid, max(AcquisitionCost) as AcquisitionCost, prefar.assetid from prefar INNER JOIN
(select Assetid from (select distinct subassetid, assetid from PreFAR where AssetID in
(Select assetId from prefar where companyid=42 and assetstage>=2 and isactive=1 group by assetid having count(*) >1))
as tbl1 group by assetid having count(*) >1) as grpTbl ON prefar.AssetId = grpTbl.AssetId where prefar.companyid=42
and prefar.assetstage>=2 and prefar.isactive=1 Group by prefarid) as tbl
Group by tbl.assetid HAVING MAX(tbl.AcquisitionCost)) as tbl2 on p2.AssetID=tbl2.assetid
where p2.AcquisitionCost=tbl2.AcquisitionCost)) as tblProcessed ON tblProcessed.Assetid = P.AssetId
SET P.MergeId = CASE When P.MergeId > 0 THEN P.MergeId Else
(Case When CAST(P.AcquisitionCost AS DECIMAL(10,2)) > CAST(tblProcessed.AcquisitionCost AS DECIMAL(10,2))
Then P.MergeId Else tblProcessed.prefarid End) END
where P.companyid=42 and P.assetstage>=2 and P.isactive=1
this query runs on toad or workbench but iin visual studio gives exceptoin
SELECT list is not in GROUP BY clause and contains nonaggregated column … incompatible with sql_mode=only_full_group_by please help