如何使用oracle限制要更新的列数? [英] how to restrict number of columns to be update using oracle?

问题描述

我使用的是 Oracle 11g.我想限制要更新的列数.我只想更新 300 名学生的 Fee_Call_Opt_uid 列.一旦我使用 rownum <300 它抛出并出错.

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 错误:

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

推荐答案

啊，是的...又一个引发未知错误的查询.

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>

轮到你了.

又轮到我了:从 rownum 中删除表别名，这会导致问题(正如 SQL*Plus 用星号很好地指出的那样):

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>

这篇关于如何使用oracle限制要更新的列数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！