CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
abdul raheem dev
abdul raheem dev

Reputation: 29

how to restrict number of columns to be update using oracle?

I am using Oracle 11g. I want to restrict number of columns to be updated. I want to update only 300 students Fee_Call_Opt_uid column. Once I use rownum < 300 it throws and error.

UPDATE student st
   SET st.Fee_Call_Opt_uid =
       (SELECT t.emp_id
          FROM (SELECT DISTINCT eco.emp_id, ct.city_name, con.country_name
                  FROM emp_call_opt eco
                  JOIN territory tr
                    ON tr.territory_id = eco.territory_id
                  JOIN city ct
                    ON ct.territory_id = eco.territory_id
                  JOIN country con
                    ON con.country_id = ct.country_id) t
         WHERE st.city = t.city_name
           AND st.country = t.country_name)
 WHERE st.rownum < 300
   AND st.Fee_Call_Opt_uid IS NULL;

SQL Error:

ORA-01747: invalid user.table.column, table.column, or column specification

Upvotes: 0

Views: 191

Answers (2)

Littlefoot
Littlefoot

Reputation: 142778

Ah, yes ... yet another query which raises unknown error(s).

To me, it works as expected. Simplified:

SQL> update student set fee_call_opt_uid = 'x' where rownum <= 10;

10 rows updated.

SQL>

Your turn.

My turn again: remove table alias from rownum, it causes problems (as SQL*Plus nicely points out with an asterisk):

SQL> update student s set s.fee_call_opt_uid = 1 where s.rownum <= 10;
update student s set s.fee_call_opt_uid = 'x' where s.rownum <= 10
                                                    *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

SQL> update student s set s.fee_call_opt_uid = 'x' where rownum <= 10;

10 rows updated.

SQL>

Upvotes: 0

Atif
Atif

Reputation: 2210

Dont use st.rownum . Rownum is a pseudocolumn and does not belongs to any table.

Upvotes: 1

Related Questions