Home » SQL & PL/SQL » SQL & PL/SQL » Sql to Update common rows in 2 tables (Oracle 10g)
Sql to Update common rows in 2 tables [message #291525] Fri, 04 January 2008 13:32 Go to next message
msharma
Messages: 6
Registered: January 2008
Junior Member
Hi,

I have sql which finds out the common rows in 2 tables. These tables t1 and t2 are different in structure and have different columns. I SUM values in t1 and compare them against columns in t2. My sql to do this is as follows:

SQL1:

(select SUM(t1.dollars), SUM(t1.calls), SUM(t1.minutes),
t1.prod_type
from t1 where ....
group by t1.prod_type

INTERSECT

select t2.dollars, t2.calls, t3.minutes
from t2 where....
group by t2.prod_type

);

This gives me the rows which are common in both tables. I now want to update these rows in t1 and t2 which are matching. Both tables have a primary key -lets say t1_id and t2_id.

I am trying to write something like

SQL2:

update t1 set t1.status = 1
where t1.t1_id IN(
....)


However, i just don't know how to continue. How can I select the t1_id of the matching rows in t1, if I add it in SQL1 then i don't get the matching rows!

I am really stuck and would really appreciate any help!

Thanks in advance.

Regards.
Re: Sql to Update common rows in 2 tables [message #291527 is a reply to message #291525] Fri, 04 January 2008 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL1 can't work, it must return a syntax error as there are not the same number of columns.

You have to eother return ids in SQL1 or make the correlation with the columns that give you SQL1 if you want then to update t1 and t2.

Regards
Michel
Re: Sql to Update common rows in 2 tables [message #291528 is a reply to message #291527] Fri, 04 January 2008 13:58 Go to previous messageGo to next message
msharma
Messages: 6
Registered: January 2008
Junior Member
HI,

I am sorry I forgot to include the prod_type column in SQL1
My SQL 1 is as below and so far it is returning the correct data.

SQL1:

(select SUM(t1.dollars), SUM(t1.calls), SUM(t1.minutes),
t1.prod_type
from t1 where ....
group by t1.prod_type

INTERSECT

select t2.dollars, t2.calls, t3.minutes,
t2.prod_type
from t2 where....
group by t2.prod_type

);

I cannot include my t1.t1_id in my select since the primary keys t1_id and t2_id are different, and if included the above sql would no longer return common rows.

Is there any way around this?
Re: Sql to Update common rows in 2 tables [message #291531 is a reply to message #291528] Fri, 04 January 2008 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So make the correlation (IN part) on SQL1 selected columns.

Regards
Michel
Re: Sql to Update common rows in 2 tables [message #291537 is a reply to message #291531] Fri, 04 January 2008 14:34 Go to previous messageGo to next message
msharma
Messages: 6
Registered: January 2008
Junior Member
HI Michel,

I am sorry, but I am not too clear on what you are suggesting.
Could you please describe with an example?

If you mean including t1.t1_id in my select form t1, I have tried this, but it does not work, since it will no longer return any rows from SQL1. t1_id and t2_id are not the same, so they cannot be included in the selected columns - since then it would produce no common rows.

I would really appreciate it if you can expand on your answer. Please pardon me, I am a junior member. Thanks for your time.
Re: Sql to Update common rows in 2 tables [message #291540 is a reply to message #291537] Fri, 04 January 2008 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Could you please describe with an example?

Can you post an example on which I can work?
For instance, a description of your tables t1 and t2 and what is the primary and unique keys.

Regards
Michel
Re: Sql to Update common rows in 2 tables [message #291559 is a reply to message #291525] Fri, 04 January 2008 17:13 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
if t1_id and t2_id are the primary keys of their respective tables, the you cannot include them anyway can you? You are doing a SUM to the prod_type level.

Please give us a verbal (that is, in words) description of what you goal is. Describe the problem, don't show us code. Then we can give you a better direction.

This is needed because it is not clear what kind of update you intend to make. Do you want to set the values on every t1 tied to a common row to the summed values and then same for t2? Don't make any sense to me, which is why we need a better descrption of your need.

Kevin
Previous Topic: Trim the spaces between the words in oracle
Next Topic: Can I create a FK from a schema user to another?
Goto Forum:
  


Current Time: Fri Dec 02 12:43:12 CST 2016

Total time taken to generate the page: 0.08769 seconds