OraFAQ Forum: SQL & PL/SQL » Update a column in table only if a condition is met for number of values for a field in other table

Home » SQL & PL/SQL » SQL & PL/SQL » Update a column in table only if a condition is met for number of values for a field in other table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update a column in table only if a condition is met for number of values for a field in other table [message #638760]

Fri, 19 June 2015 19:53

suhaas
Messages: 5
Registered: June 2015

Junior Member

I am trying to figure out how to achieve the following (This is simplified version that ignores non relevant columns)

I have table "Item" with columns

item_id business_key

I have another table "Order" with columns

order_id business_key item_id

Here is the problem statement.

For a given item_id in Item table, find orders that have only one unique business_key associated with it in Order table. Mind you, unique business_key could mean one or more rows for the unique combination of business_key and item_id. In this case, I need to take business_key derived from Order table and update the Item table with that value.

So if the Order table has values

Scenario I

Order Table order_id business_key item_id 1 apple 12 2 apple 12

Then I need to update business_key in Item table with "apple"

Scenario II

However, Order table has values order_id business_key item_id 1 apple 12 2 orange 12

Nothing needs to be done. business_key in Item table needs no update (it is null by default)

What is the best approach. I have considered subqueries, Oracle Merge but I have not come up with a solution. Is PL/SQL the only answer?

Report message to a moderator

Re: Update a column in table only if a condition is met for number of values for a field in other table [message #638761 is a reply to message #638760]

Fri, 19 June 2015 20:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

>What is the best approach
Which metric measures best? What is unit of measure for best.

Never do in PL/SQL that which can be done in plain SQL.

Report message to a moderator

Re: Update a column in table only if a condition is met for number of values for a field in other table [message #638762 is a reply to message #638760]

Fri, 19 June 2015 20:09

John Watson
Messages: 9003
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
You need to provide te CREATE TABLE and INSERT statements needed to set up the problem.
Quote:

I have considered subqueries, Oracle Merge but I have not come up with a solution.

What have you tried? This sounds like a college homework question, you do need to have a go yourself, then ask for help when you get stuck.

Report message to a moderator

Re: Update a column in table only if a condition is met for number of values for a field in other table [message #638763 is a reply to message #638762]

Fri, 19 June 2015 22:19

suhaas
Messages: 5
Registered: June 2015

Junior Member

You are supposedly a Senior Member on this forum. I did not expect sucha response. HArdly helpful and very discouraging. I have seen much simpler questions being answered on other forums.

Report message to a moderator

Re: Update a column in table only if a condition is met for number of values for a field in other table [message #638764 is a reply to message #638763]

Fri, 19 June 2015 23:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

suhaas wrote on Fri, 19 June 2015 20:19

You are supposedly a Senior Member on this forum. I did not expect sucha response. HArdly helpful and very discouraging. I have seen much simpler questions being answered on other forums.

Above provides no additional details needed to provide any answer.
Since we have NO tables, NO data & NO known results, we can't guess what SQL is requested.

You have 2 options choose between now.
Post the requested details here or post to other forum & hope to get lucky.

I wonder how you are qualified to determine what is simpler when you are struggling & looking for answers.

Report message to a moderator

Re: Update a column in table only if a condition is met for number of values for a field in other table [message #638765 is a reply to message #638764]

Fri, 19 June 2015 23:42

suhaas
Messages: 5
Registered: June 2015

Junior Member

Black Swan, I have never come across such discouraging response. Why did you find it necessary to send em a private message over and above what you have said here? I am still shaking my head. What kind of forum this is?

Report message to a moderator