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

 

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