10-08-2014 01:14 PM
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:
PARENT_LINK (POINTS TO PARENT OPPPRODUCTID)
This is a simplified version of my query that works in SQL Management Studio
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.
Solved! Go to Solution.
10-08-2014 01:33 PM - edited 10-08-2014 01:34 PM
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.
10-08-2014 03:08 PM
That worked Mike, thanks very much:
SET AM_AMT = (SELECT OP.EXTENDEDPRICE/2 FROM OPPORTUNITY_PRODUCT OP WHERE OP.OPPPRODUCTID = OPPORTUNITY_PRODUCT_EX.PARENT_LINK)
WHERE OPPPRODUCTID IN ('NNNNNNNNNNNN'...)