ITPub博客

首页 > 应用开发 > IT综合 > Update statement and Null-values(zt from asktom)

Update statement and Null-values(zt from asktom)

原创 IT综合 作者:jametong 时间:2005-04-14 00:14:53 0 删除 编辑
David -- Thanks for the question regarding "Update statement and Null-values -- update a table from another table", version Oracle 7.3.4
originally submitted on 5-Jun-2000 10:18 Eastern US time, last updated 13-Apr-2005 11:48Tom's latest followup | GOTO a Bookmarkable Page | Bottom

You Asked (Jump to Tom's latest followup)

Tom,

When updating a column with an update-statement, the value of some records
(records that don't need to be updated), are changed into the value NULL. I use
the next statement:
update    table    name B
  set        columnname    =
    (    select    value
          from    lookup    O
          where    B.keyname = O.keyname
            and    O.Othercolumn = Other_value);

As a result all the necessary changes are made, but also the records that don't
need to be updated: they get the Null-value. Is there a way of avoiding this,
because we do need to update the records frequently, but not all records at the
same time.
Is there a kind of workaround we can use for updating the records that need to
be updated without changing the other records too with a Null value?

Thanks and greetings,

David Boot
 

 
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@]

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

请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280598