Forum Discussion

KrzysiekBojak's avatar
KrzysiekBojak
Copper Contributor
Apr 05, 2022

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...
OPENQUERY
KrzysiekBojak's avatar
KrzysiekBojak
Copper Contributor
Apr 05, 2022

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 > 0
KrzysiekBojak's avatar
KrzysiekBojak
Copper Contributor
Apr 05, 2022

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 ??

Resources