5/3/2024 7:31:12 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
failed to parse
Posted: 30 Apr 09 3:18 PM
|
Help
I've been trying to write an update script all day and have tried many versions using suggestions from this site, but all fail with a "failed to parse" statement.
Here are a few versions, all of which work in sql analyser, none of which work in SLX:
Update A1
SET A1.CLIENT_SINCE = (select INCUMBENT_SINCE from C_ACCOUNTSERVICES A2
where A2.C_ACCOUNTSERVICESID = A1.C_ACCOUNTSERVICESID)
FROM C_ACCOUNTSERVICES A1, product product, C_COMPETITOR_EXT competitor
where product.PRODUCTID = A1.PRODUCTID
and PRODUCT.name not like '*TOT%'
and COMPETITOR.COMPETITORID = A1.INCUMBENT
and COMPETITOR.COMPETITOR_GROUP like '%Sodexho%'
and A1.INCUMBENT_SINCE is not null
--_________________________________________
Update A1
SET A1.CLIENT_SINCE = (select INCUMBENT_SINCE from C_ACCOUNTSERVICES A2
where A2.C_ACCOUNTSERVICESID = A1.C_ACCOUNTSERVICESID)
FROM C_ACCOUNTSERVICES A1
where A1.C_ACCOUNTSERVICESID =
(select C_ACCOUNTSERVICESID from C_ACCOUNTSERVICES A3, product product, C_COMPETITOR_EXT competitor
where
A3.C_ACCOUNTSERVICESID = A1.C_ACCOUNTSERVICESID
and product.PRODUCTID = A3.PRODUCTID
and PRODUCT.name not like '*TOT%'
and COMPETITOR.COMPETITORID = A3.INCUMBENT
and COMPETITOR.COMPETITOR_GROUP like '%Sodexho%'
and A3.INCUMBENT_SINCE is not null)
--_________________________________________
Update A1
SET A1.CLIENT_SINCE = A1.INCUMBENT_SINCE
FROM C_ACCOUNTSERVICES A1
where A1.C_ACCOUNTSERVICESID =
(select C_ACCOUNTSERVICESID from C_ACCOUNTSERVICES A3, product product, C_COMPETITOR_EXT competitor
where
A3.C_ACCOUNTSERVICESID = A1.C_ACCOUNTSERVICESID
and product.PRODUCTID = A3.PRODUCTID
and PRODUCT.name not like '*TOT%'
and COMPETITOR.COMPETITORID = A3.INCUMBENT
and COMPETITOR.COMPETITOR_GROUP like '%Sodexho%'
and A3.INCUMBENT_SINCE is not null)
--_________________________________________
Update A1
SET A1.CLIENT_SINCE = (
select INCUMBENT_SINCE from C_ACCOUNTSERVICES A2
where A2.C_ACCOUNTSERVICESID = A1.C_ACCOUNTSERVICESID
)
FROM C_ACCOUNTSERVICES A1
where A1.C_ACCOUNTSERVICESID in (
select C_ACCOUNTSERVICESID
from C_ACCOUNTSERVICES A3
inner join product product on product.PRODUCTID = A3.PRODUCTID
inner join C_COMPETITOR_EXT competitor on COMPETITOR.COMPETITORID = A3.INCUMBENT
where
PRODUCT.name not like '*TOT%'
and COMPETITOR.COMPETITOR_GROUP like '%Sodexho%'
and A3.INCUMBENT_SINCE is not null
) |
|
|
|
Re: failed to parse
Posted: 30 Apr 09 3:54 PM
|
Don't use Joins, just subqueries:
Update A1
SET A1.CLIENT_SINCE = (select INCUMBENT_SINCE from C_ACCOUNTSERVICES A2
where A2.C_ACCOUNTSERVICESID = A1.C_ACCOUNTSERVICESID)
FROM C_ACCOUNTSERVICES A1, product product, C_COMPETITOR_EXT competitor
where product.PRODUCTID = A1.PRODUCTID
and PRODUCT.name not like '*TOT%'
and COMPETITOR.COMPETITORID = A1.INCUMBENT
and COMPETITOR.COMPETITOR_GROUP like '%Sodexho%'
and A1.INCUMBENT_SINCE is not null
Try this one instead
UPDATE C_ACCOUNTSERVICES
SET CLIENT_SINCE = (select INCUMBENT_SINCE from C_ACCOUNTSERVICES where C_ACCOUNTSERVICESID = C_ACCOUNTSERVICES.C_ACCOUNTSERVICESID)
WHERE PRODUCTID IN
(SELECT PRODUCTID FROM PRODUCT WHERE NAME NOT LIKE '*TOT%')
AND INCUMBENT IN
(SELECT COMPETITORID FROM C_COMPETITOR_EXT WHERE COMPETITOR_GROUP LIKE '%SODEXHO%')
|
|
|
|
Re: failed to parse
Posted: 30 Apr 09 4:41 PM
|
The OLEDB provider does not support UPDATE FROM statements. However, I dont think you need anything complicated as it looks like you are trying to move a column value to another column on the same row. If not, I do not follow why you are using a subselect based on the primary key. If you are simply trying to populate the CLIENT_SINCE column with the value from the INCUMBENT_SINCE column where the record matches your criteria the following may be what you want (thoroughly test in a dev environment before running on production):
UPDATE
C_ACCOUNTSERVICES
SET
CLIENT_SINCE = INCUMBENT_SINCE
WHERE
C_ACCOUNTSERVICESID IN
(
SELECT
C_ACCOUNTSERVICESID
FROM
C_ACCOUNTSERVICES CAS
INNER JOIN PRODUCT P ON P.PRODUCTID = CAS.PRODUCTID
INNER JOIN C_COMPETITOR_EXT CPE ON CPE.COMPETITORID = CAS.INCUMBENT
WHERE
P.NAME NOT LIKE '*TOT%'
AND CPE.COMPETITOR_GROUP LIKE '%SODEXHO%'
AND CAS.INCUMBENT_SINCE IS NOT NULL
)
If this is not what you want please elaborate.
HTH!
Timmus |
|
|
|
Re: failed to parse
Posted: 01 May 09 8:42 AM
|
The Timmus one works. My first statement, which I did not post above, didn't have a FROM in the UPDATE. It didn't work and somehow I came to think that for SLX I HAD to have an UPDATE FROM, so all my further attempts included that. I don't have a copy of the first attempt anymore and don't remember how it differed from the solution.
Thanks for the help!! |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|