INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle update one table based on value in another - Can't get this right.

Oracle update one table based on value in another - Can't get this right.

Oracle update one table based on value in another - Can't get this right.

(OP)
Business case:

Table1 did not get updated by someone elses data load, so I have to fix it. (Yay!)

Anyway, here is the case.

Table1.hrc needs updated to match Table2.hrc with the following conditions:
Table1.num = Table2.num
and Table2.status = 'HOLD'
and Table1.hrc is null
and Table2.hrc is not null

I know this should be easy, but I can't get it to return a single row(always multiple). The following query returns the records I need to update, I just can't get the update to run as this selects multiple records.

select Table2.num, Table1.num, Table2.hrc, Table1.hrc, Table2.status
from Table2 w, Table1 s
where Table2.hrc is not null
and Table1.hrc is null
and Table2.status = 'HOLD'
and Table2.num = Table1.num
and Table2.siteid = Table1.siteid
group by Table2.num, Table1.num, Table2.hrc, Table1.hrc, Table2.status;

Thanks in advance.

RE: Oracle update one table based on value in another - Can't get this right.

First things first. You're not doing any aggregation so your group by clause is redundant. Secondly you don't use the correalation names w and s anywhere so ditch those also. Next thing is, see if you can use updateable join view syntax. You might not be able to.

update(
select Table2.hrc oldhrc, Table1.hrc newhrc
from Table2 , Table1
where Table2.hrc is not null
and Table1.hrc is null
and Table2.status = 'HOLD'
and Table2.num = Table1.num
and Table2.siteid = Table1.siteid
)
set newhrc = oldhrc

If you get an non-key preserveed error when running the above you can't do it this way. No matter. It's a bit difficult to second guess update statements when you dont know whats in the tables and it would be helpful if you could provide some test data as well as table creation/insert statements. But in the meantime try this

update table1 t1
set t1.hrc = (select hrc
from table2 t2
where t1.hrc is null
and t2.siteid = t1.siteid
and t2.num = t1.num
and t2.status = 'HOLD')
and exists (select 'x' from table2
where t1.hrc is null
and siteid = t1.siteid
and num = t1.num
and status = 'HOLD')


In order to understand recursion, you must first understand recursion.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close