All Forums General
kartaj 10 posts Joined 10/15
04 Jul 2016
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.
 

M.Saeed Khurram 544 posts Joined 09/12
04 Jul 2016

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

kartaj 10 posts Joined 10/15
04 Jul 2016

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)

dnoeth 4628 posts Joined 11/04
04 Jul 2016

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
 )

 

Dieter

M.Saeed Khurram 544 posts Joined 09/12
04 Jul 2016

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

Khurram

kartaj 10 posts Joined 10/15
05 Jul 2016

Thank you Dieter...:)

You must sign in to leave a comment.