Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Contributor
Posts: 9
Registered: ‎08-12-2014
Accepted Solution

Trying to do a SQL Admin Update using a JOIN

 

Using Saleslogix 7.5.2, we have extended the Opportunity_product table as Opportunity_product_ex. For any given Opportunity product, there are two Opportunity_Product records created, one is the parent (software) and the other the child (annual maintenance). What I am trying to do with SQL Admin is update a field in the child record based on a value in the parent record.

 

 

 

This is the relevant table info:

 

OPPORTUNITY_PRODUCT

 

OPPPRODUCTID

 

CALCPRICE

 

 

OPPORTUNITY_PRODUCT_EX

 

OPPPRODUCTID

 

PARENT_LINK (POINTS TO PARENT OPPPRODUCTID)

 

AM_AMT

 

 

 

This is a simplified version of my query that works in SQL Management Studio

 

 

 

UPDATE ope_c

SET am_amt = op_p.calcprice/2

FROM opportunity_product op_p join

     opportunity_product_ex ope_c on op_p.oppproductid = ope_c.parent_link

WHERE ope_c.oppproductid in ('NNNNNNNNNNNN',...)

 

 

I have tried a variety of methods to get this to work in SQL Admin, but they are all failing with Failed to Parse SQL errors. Any help would be appreciated. Thanks.

 

Dan

 

Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Trying to do a SQL Admin Update using a JOIN

Do you have remotes or remote offices ? If not, do this via SQL Studio - it fails as 'update from' is not supported by provider.
Copper Contributor
Posts: 9
Registered: ‎08-12-2014

Re: Trying to do a SQL Admin Update using a JOIN

Yes unfortunately, we do have remotes.

 

Dan

Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Trying to do a SQL Admin Update using a JOIN

[ Edited ]

You'll need to re-write the query. Below is an example where the AccountName is being updated. This could be done easily in an Update/From - but this version works within Admin and syncs to remotes.

 

UPDATE HISTORY
  SET ACCOUNTNAME = (SELECT A.ACCOUNT FROM ACCOUNT A WHERE HISTORY.ACCOUNTID = A.ACCOUNTID)
WHERE
  HISTORYID IN (SELECT HISTORYID from HISTORY h INNER JOIN ACCOUNT a ON (a.ACCOUNTID = h.ACCOUNTID AND a.ACCOUNT <> h.ACCOUNTNAME))

 

 

Basically, get the value from a Select and then join it in the Where.

Copper Contributor
Posts: 9
Registered: ‎08-12-2014

Re: Trying to do a SQL Admin Update using a JOIN

Thanks Mike, I will see if I can make my code match the style of your example.

 

Dan

Copper Contributor
Posts: 9
Registered: ‎08-12-2014

Re: Trying to do a SQL Admin Update using a JOIN

That worked Mike, thanks very much:

 

 

UPDATE OPPORTUNITY_PRODUCT_EX

SET AM_AMT = (SELECT OP.EXTENDEDPRICE/2 FROM OPPORTUNITY_PRODUCT OP WHERE OP.OPPPRODUCTID = OPPORTUNITY_PRODUCT_EX.PARENT_LINK)

WHERE OPPPRODUCTID IN ('NNNNNNNNNNNN'...)

 

 

Dan