Forum Discussion
Update OPENQUERY linked server
Hello. I have a massive request for help. I didn't sleep through the night trying to UPDATE OPENQUERY linked server. INSER and DELET was easy. However, I cannot cope with UPDATE. --GO -- usuw...
BINGO !!!!
It succeeded.
I solved my problme;))
UPDATE QQ
SET QQ.products_quantity = CT.Grupa_Id
FROM OPENQUERY (ImageArte, 'SELECT * FROM subiekt_stany') QQ
JOIN OPENQUERY (ImageArte, 'SELECT * FROM subiekt_stany') QQ2 ON QQ.products_model = QQ2.products_model
INNER JOIN [Nexo_IMAGE]. [DataModelContainer]. [Assortments] CT ON CT.Symbol = QQ.products_model
WHERE QQ2.products_model = CT.Symbol
maybe it will be useful to someone
I found a solution in another forum
UPDATE OQ
SET OQ.Delivered='YES'
FROM OPENQUERY(LINK_DB, 'SELECT * FROM XXXXR.HLIB.O1') OQ
JOIN OPENQUERY(LINK_DB, 'SELECT * FROM XXXXR.HLIB.O2') QQ2 ON QQ.ItemID = QQ2.ItemID
INNER JOIN ConfirmTable CT ON CT.Barcode = OQ.Barcode
WHERE QQ2.Qty > 0I have 1 problem left
This is the most important part - counting the quantity
MYSQL.products_quantity = cast(CASE WHEN (COALESCE(sum(MssqlStan.IloscDostepna),0)-COALESCE(sum(MssqlStan.IloscZadysponowana),0)) > 0 THEN (COALESCE(sum(MssqlStan.IloscDostepna),0)-COALESCE(sum(MssqlStan.IloscZadysponowana),0)) ELSE 0 END as int)I'll get an error
Msg 157, Level 15, State 1, Line 15An aggregate may not appear in the set list of an UPDATE statement.UPDATE MYSQL SET MYSQL.purchase_price = cast(MssqlAsortyment.[CenaEwidencyjna]*1.23 as decimal (15,2)) , MYSQL.products_price_g = cast(c.CenaBrutto as decimal(15,2)) , MYSQL.minimum_price=cast(c1.CenaNetto as decimal(15,2)), MYSQL.products_weight= cast(MssqLMiara.Masa as decimal (6,3)), MYSQL.products_quantity = cast(CASE WHEN (COALESCE(sum(MssqlStan.IloscDostepna),0)-COALESCE(sum(MssqlStan.IloscZadysponowana),0)) > 0 THEN (COALESCE(sum(MssqlStan.IloscDostepna),0)-COALESCE(sum(MssqlStan.IloscZadysponowana),0)) ELSE 0 END as int) FROM OPENQUERY(ImageArte, 'SELECT * FROM subiekt_stany') MySQL JOIN OPENQUERY(ImageArte, 'SELECT * FROM subiekt_stany') MySQL2 ON MySQL.products_model = MySQL2.products_model INNER JOIN [Nexo_IMAGE].[ModelDanychContainer].[Asortymenty] MssqlAsortyment ON MssqlAsortyment.Symbol = MySQL.products_model inner join [Nexo_IMAGE].[ModelDanychContainer].[PozycjeCennika] c on MssqlAsortyment.Id=c.Asortyment_Id inner join [Nexo_IMAGE].[ModelDanychContainer].[PozycjeCennika] c1 on c.[Asortyment_Id]=c1.[Asortyment_Id] and c.[Cennik_Id]=100016 and c1.[Cennik_Id]=100017 inner join [Nexo_IMAGE].[ModelDanychContainer].[JednostkiMiarAsortymentow] MssqLMiara on MssqlAsortyment.id =MssqLMiara.Asortyment_Id left join [Nexo_IMAGE].[ModelDanychContainer].[StanyMagazynowe] MssqlStan on MssqlAsortyment.Id=MssqlStan.Asortyment_Id WHERE MySQL2.products_model = MssqlAsortyment.SymbolAny suggestion ??