CODEWITHSUNDEEP
sqloracle11gsqlpluswith-statement
Jallal
Jallal

Reputation: 101

SQL with WITH clause too many values error

--This query is returning a "too many values" error. I'm not sure why? 

   update myTable
            set var1= var2
            WHERE value1 IN (

             WITH X AS
            (
            select value1, value2, var1,var2, ROW_NUMBER()
            OVER
            (PARTITION BY value1 ORDER BY value1 desc) as rn
            from
              mytable WHERE var1 is null AND rownum>0 and rownum<=10 order by value1 asc
            )
            SELECT
            value1, value2, var2,var1
           FROM X WHERE rn=1  and var1 is null and rownum>0 and rownum<=10);

Upvotes: 0

Views: 219

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think your intention is better served using exists:

update myTable
    set var1 = var2
    where exists (select 1
                  from (select value1, value2, var1, var2, 
                               ROW_NUMBER() OVER (PARTITION BY value1 ORDER BY value1 desc) as rn
                       from mytable 
                       where var1 is null
                      ) x
                  where mytable.value1 in (x.value1, x.value2, x.var1, x.var2) and
                        rn <= 10
                 );

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

The error too many values is because the query has value1 IN () where multiple columns are being selected in the inner query. To avoid this, just select value1 in the inner query.

update myTable
set var1 = var2
WHERE value1 IN
     (select value1 from 
       (select value1, value2, var1,var2, 
        ROW_NUMBER() OVER (PARTITION BY value1 ORDER BY value1 desc) as rn
        from mytable 
        WHERE var1 is null
        ) t 
      where rn <= 10
      )

Upvotes: 1

Related Questions