Update statment is getting failed

General
Enthusiast

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
Enthusiast

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
Enthusiast

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)

Senior Apprentice

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

Re: Update statment is getting failed

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

Khurram
Enthusiast

Re: Update statment is getting failed

Thank you Dieter...:)