from asktom

and we said...

There are at least 2 ways to perform this sort of co-related update correctly.  I'll show 
my preferred method (update a join) and then another method that'll work if you cannot 
put a unique constraint on LOOKUP(keyname) (which is needed for the join update).


Here are the test tables:

scott@ORA734.WORLD> create table name
  2  ( keyname int,
  3    columnName varchar2(25)
  4  )
  5  /
Table created.

scott@ORA734.WORLD> create table lookup
  2  ( keyname int PRIMARY KEY,
  3    value varchar2(25),
  4    otherColumn int
  5  )
  6  /
Table created.

scott@ORA734.WORLD> insert into name values ( 100, 'Original Data' );
1 row created.

scott@ORA734.WORLD> insert into name values ( 200, 'Original Data' );
1 row created.

scott@ORA734.WORLD> insert into lookup values ( 100, 'New Data', 1 );
1 row created.

scott@ORA734.WORLD> commit;
Commit complete.


here is the "other_value" parameter you are using in the above update you 
attempted...

scott@ORA734.WORLD> variable other_value number
scott@ORA734.WORLD> exec :other_value := 1
PL/SQL procedure successfully completed.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 Original Data
       200 Original Data


Here we update a join.  We can only modify the columns in one of the tables and the 
other tables we are *NOT* modifying must be "key preserved" -- that is, we must be able 
to verify that at most one record will be returned when we join NAME to this other table. 
 In order to do that, keyname in LOOKUP must either be a primary key or have a unique 
constraint applied to it...

scott@ORA734.WORLD> update
  2    ( select columnName, value
  3        from name, lookup
  4       where name.keyname = lookup.keyname
  5         and lookup.otherColumn = :other_value )
  6     set columnName = value
  7  /

1 row updated.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 New Data
       200 Original Data

See, the other data is untouched and only the rows we wanted are updated..

scott@ORA734.WORLD> rollback;
Rollback complete.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 Original Data
       200 Original Data


Now, this way will work with no constraints on anything -- you do not need the primary 
key/unique constraint on lookup (but you better be sure the subquery returns 0 or 1 
records!).

It is very much like your update, just has a where clause so that only rows that we find 
matches for are actually updated...

scott@ORA734.WORLD> update name
  2     set columnName = ( select value
  3                          from lookup
  4                         where lookup.keyname = name.keyname
  5                           and otherColumn = :other_value )
  6   where exists ( select value
  7                    from lookup
  8                   where lookup.keyname = name.keyname
  9                     and otherColumn = :other_value )
 10  /

1 row updated.

scott@ORA734.WORLD> select * from name;

   KEYNAME COLUMNNAME
---------- -------------------------
       100 New Data
       200 Original Data
 
[@more@]from

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94041/viewspace-926387/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/94041/viewspace-926387/

内容概要:文章详细介绍了ETL工程师这一职业,解释了ETL(Extract-Transform-Load)的概念及其在数据处理中的重要性。ETL工程师负责将分散、不统一的数据整合为有价值的信息,支持企业的决策分析。日常工作包括数据整合、存储管理、挖掘设计支持和多维分析展现。文中强调了ETL工程师所需的核心技能,如数据库知识、ETL工具使用、编程能力、业务理解能力和问题解决能力。此外,还盘点了常见的ETL工具,包括开源工具如Kettle、XXL-JOB、Oozie、Azkaban和海豚调度,以及企业级工具如TASKCTL和Moia Comtrol。最后,文章探讨了ETL工程师的职业发展路径,从初级到高级的技术晋升,以及向大数据工程师或数据产品经理的横向发展,并提供了学习资源和求职技巧。 适合人群:对数据处理感兴趣,尤其是希望从事数据工程领域的人士,如数据分析师、数据科学家、软件工程师等。 使用场景及目标:①了解ETL工程师的职责和技能要求;②选择适合自己的ETL工具;③规划ETL工程师的职业发展路径;④获取相关的学习资源和求职建议。 其他说明:随着大数据技术的发展和企业数字化转型的加速,ETL工程师的需求不断增加,尤其是在金融、零售、制造、人工智能、物联网和区块链等领域。数据隐私保护法规的完善也使得ETL工程师在数据安全和合规处理方面的作用更加重要。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值