Update OPENQUERY linked server

[KrzysiekBojak]

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 -- usuwanie zawartości tabeli 
--DELETE FROM OPENQUERY(ImageArte, 'SELECT * FROM subiekt_stany')

--Go -- Wstawianie do tabeli 
--INSERT INTO OPENQUERY(ImageArte, 'SELECT * FROM subiekt_stany')
--(products_model,products_quantity,purchase_price,minimum_price,products_price_g,products_weight)

---- START UPDATE ----

UPDATE MYSQL
SET    MYSQL.products_weight = a.products_weight 
FROM   OPENQUERY(ImageArte, 'SELECT products_quantity FROM subiekt_stany') MYSQL
       INNER JOIN [Nexo_IMAGE].[ModelDanychContainer].[Asortymenty] a
         ON a.products_model = MYSQL.products_model 


--- END UPDATE ---

SELECT 
	cast(a.[Symbol] AS varchar) products_model,
	cast(CASE 
			WHEN (COALESCE(sum([Nexo_IMAGE].[ModelDanychContainer].[StanyMagazynowe].[IloscDostepna]),0)-COALESCE(sum([Nexo_IMAGE].[ModelDanychContainer].[StanyMagazynowe].[IloscZadysponowana]),0)) > 0 THEN (COALESCE(sum([Nexo_IMAGE].[ModelDanychContainer].[StanyMagazynowe].[IloscDostepna]),0)-COALESCE(sum([Nexo_IMAGE].[ModelDanychContainer].[StanyMagazynowe].[IloscZadysponowana]),0))
			ELSE 0
		END as int) products_quantity,
	cast(a.[CenaEwidencyjna]*1.23 as decimal (15,2)) purchase_price,
	cast(c1.CenaNetto as decimal(15,2)) minimum_price,
    cast(c.[CenaBrutto] as decimal(15,2)) products_price_g,
	cast([Nexo_IMAGE].[ModelDanychContainer].[JednostkiMiarAsortymentow].[Masa] as decimal (6,3)) products_weight

FROM [Nexo_IMAGE].[ModelDanychContainer].[PozycjeCennika] c

	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].[Asortymenty] a
		on c.[Asortyment_Id]=a.[Id]

	inner join [Nexo_IMAGE].[ModelDanychContainer].[JednostkiMiarAsortymentow]
		on a.[Id]=[Nexo_IMAGE].[ModelDanychContainer].[JednostkiMiarAsortymentow].[Asortyment_Id]

	left join [Nexo_IMAGE].[ModelDanychContainer].[StanyMagazynowe]
		on a.[Id]=[Nexo_IMAGE].[ModelDanychContainer].[StanyMagazynowe].[Asortyment_Id]
	
	inner join [Nexo_IMAGE].[ModelDanychContainer].[GrupyAsortymentu]
		on a.[Grupa_Id]=[Nexo_IMAGE].[ModelDanychContainer].[GrupyAsortymentu].[Id]

	inner join [Nexo_IMAGE].[ModelDanychContainer].[NaglowkiEncji]
		on a.[Naglowek_Id]=[Nexo_IMAGE].[ModelDanychContainer].[NaglowkiEncji].[Id]

WHERE

	[Nexo_IMAGE].[ModelDanychContainer].[GrupyAsortymentu].[Nazwa] Like 'Z – %' and
	a.[IsInRecycleBin]=0 and
	(a.[SprzedazMobilna]=1 or a.[SklepInternetowy]=1) and
	cast([Nexo_IMAGE].[ModelDanychContainer].[NaglowkiEncji].[Zmieniono] as datetime2(0)) > cast(DATEADD(HOUR, -5, GETDATE()) as datetime2(0))
	
group by

	c.Asortyment_Id,
	a.Symbol,
	a.CenaEwidencyjna,
	c.CenaBrutto,
	c1.CenaNetto,
	Nexo_IMAGE.ModelDanychContainer.JednostkiMiarAsortymentow.Masa

I don't know how to bind WHERE in UPDATE OPENQUERY with MsSQL query

[#2]

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

[#3]

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