Forum Discussion
KrzysiekBojak
Apr 05, 2022Copper Contributor
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...
KrzysiekBojak
Apr 05, 2022Copper Contributor
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 > 0KrzysiekBojak
Apr 05, 2022Copper Contributor
I 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 15
An 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.Symbol
Any suggestion ??