Re: Update records in table

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 10 Jan 2005 11:14:33 -0800
Message-ID: <115384473.0000cf0a.010@drn.newsguy.com>

In article <1105382104.821805.168520_at_f14g2000cwb.googlegroups.com>, yey2000 says...
>
>I have to run an SQL to update records in a tableA
>
>The SQL will look like this:
>
>UPDATE TableA set POST_ID = TableB.DIVISION_ID
>where TableA.CREATED_BY = TableC.Id and TableC.POSITION = TableB.Id
>
>But I get an error saying "Invalid identifier".
>Any idea how to fix this SQL.
>
>Thanks a lot
>
>Regards
>Yi
>

well, you don't see to reference tableb or tablec in there?

Here is one approach:

ops$tkyte_at_ORA9IR2> update t1

  2     set post_id = (select t2.division_id
  3                      from t2, t3
  4                     where t3.id = t1.created_by
  5                       and t3.position = t2.id )
  6   where exists
  7                   (select t2.division_id
  8                      from t2, t3
  9                     where t3.id = t1.created_by
 10                       and t3.position = t2.id )

 11 /  

0 rows updated.  

Or assuming you have the proper primary key/unique constraints that must be in place (else the above could return "subquery returns more than 1 row"), you can:

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> alter table t2 add constraint t2_unique_id unique(id);  

Table altered.  

ops$tkyte_at_ORA9IR2> alter table t3 add constraint t3_unique_id unique(id);  

Table altered.  

ops$tkyte_at_ORA9IR2>
ops$tkyte_at_ORA9IR2> update ( select t1.post_id, t2.division_id

  2             from t1, t2, t3
  3            where t1.created_by = t3.id
  4              and t3.position = t2.id )
  5     set post_id = division_id;
 

0 rows updated.

update the join.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Mon Jan 10 2005 - 13:14:33 CST