Update statment is getting failed

General
N/A

Update statment is getting failed

Hi team,

I was trying to update sample 100  active records with a specific date('2016-07-04'), But my query is getting failed...(i have tried using with qualify rank, even it was not working), can you please help me with the query

Query :

UPDATE TABLE_NM SET EFF_STRT_DT = '2016-07-04' WHERE EFF_END_DT = '9999-12-31' AND COL1 IN

(SELECT COL1 FROM TABLE_NM WHERE EFF_END_DT = '9999-12-31'  AND EFF_STRT_DT NOT IN ('2016-07-04') sample 100)

Error: SAMPLE clause is not allowed in subqueries. UPDATE Command Failed.

5 REPLIES

Re: Update statment is getting failed

Hi,

It is very clear message, SAMPLE is not allowed in subquries. remove this sample caluse:

UPDATE TABLE_NM SET EFF_STRT_DT = '2016-07-04' WHERE EFF_END_DT = '9999-12-31' AND COL1 IN
(SELECT COL1 FROM TABLE_NM WHERE EFF_END_DT = '9999-12-31' AND EFF_STRT_DT NOT IN ('2016-07-04'))

Br,

Khurram

Khurram
N/A

Re: Update statment is getting failed

Thanks for your revert Khurram,

can you please suggest ,if there is any alternate way to write the update query (which reaches the above recuriment)

N/A

Re: Update statment is getting failed

SAMPLE is not allowed in subqueries (probably because this might be a correlated subquery), but in Derived Tables:

UPDATE TABLE_NM SET EFF_STRT_DT = '2016-07-04'
WHERE EFF_END_DT = '9999-12-31'
AND COL1 IN
(
SELECT *
FROM
( SELECT COL1
FROM TABLE_NM
WHERE EFF_END_DT = '9999-12-31'
AND EFF_STRT_DT NOT IN ('2016-07-04')
sample 100
) AS dt
)

Re: Update statment is getting failed

Thank you Dieter. It is an addition to my knowledge as well :)

Khurram
N/A

Re: Update statment is getting failed

Thank you Dieter...:)