Home » SQL & PL/SQL » SQL & PL/SQL » How to Update the Col2 for below Table Structure
How to Update the Col2 for below Table Structure [message #674894] Thu, 21 February 2019 04:41 Go to next message
sonudev
Messages: 26
Registered: November 2009
Location: bangalore
Junior Member
Hi,

We have the below structure for two tables. Now I want to sort out the below queries.

Tab1
C1 C2 C3
10 20 78
70 50 76
80 90 79

Tab2
C1 C2
10 X
70 Y
80 Z

How can we update C2 column of tab1 with the values as per second table tab2 ?
How to get max value from the table tab1?

Appreciate for valuable suggestions.

Thanks,
Sonu


Re: How to Update the Col2 for below Table Structure [message #674895 is a reply to message #674894] Thu, 21 February 2019 04:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Correlated sub-query:
UPDATE tab1
SET c2 = (SELECT c2 FROM tab2 WHERE tab2.c1 = tab1)
WHERE c1 IN (SELECT c1 FROM tab2);
Merge:
MERGE INTO tab1 t1
USING (SELECT c1, c2
       FROM tab2) t2
ON t1.c1 = t2.c1
WHEN MATCHED THEN
  UPDATE SET t1.c2 = t2.c2

As for max:
SELECT max(whatever column) FROM table;
Re: How to Update the Col2 for below Table Structure [message #674897 is a reply to message #674895] Thu, 21 February 2019 05:05 Go to previous messageGo to next message
sonudev
Messages: 26
Registered: November 2009
Location: bangalore
Junior Member
Thanks Cookiemonster for your suggestions.

For the second part, can we get maximum value from that table randomly(not column wise), like 90 is maximum for that table?
Re: How to Update the Col2 for below Table Structure [message #674899 is a reply to message #674897] Thu, 21 February 2019 06:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
random max is meaningless.

If you mean the maximum value across all the numeric columns (which is not at all random) then you would max all the numeric columns and then apply greatest to the maxes.
Re: How to Update the Col2 for below Table Structure [message #674900 is a reply to message #674897] Thu, 21 February 2019 06:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sonudev wrote on Thu, 21 February 2019 03:05
Thanks Cookiemonster for your suggestions.

For the second part, can we get maximum value from that table randomly(not column wise), like 90 is maximum for that table?

If table contains columns of NUMBER, VARCHAR2 & DATE,
how to decide what is "maximum value from that table randomly (not column wise)"?
What does above even mean?
Re: How to Update the Col2 for below Table Structure [message #674901 is a reply to message #674899] Thu, 21 February 2019 06:37 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
cookiemonster wrote on Thu, 21 February 2019 06:08
random max is meaningless.

If you mean the maximum value across all the numeric columns (which is not at all random) then you would max all the numeric columns and then apply greatest to the maxes.

Unfortunately, the OP opened this thread well over a year ago and hasn't been heard from since. I seriously doubt he is still looking for an answer. Actually, I somewhat doubt if he was ever looking for an answer, since his message included a promotional statement for some "institute".
Re: How to Update the Col2 for below Table Structure [message #674902 is a reply to message #674901] Thu, 21 February 2019 06:39 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Initial post is dated today and he replied to my initial reply.

Are you confusing this thread with some other one?
Re: How to Update the Col2 for below Table Structure [message #674903 is a reply to message #674902] Thu, 21 February 2019 06:41 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
cookiemonster wrote on Thu, 21 February 2019 06:39
Initial post is dated today and he replied to my initial reply.

Are you confusing this thread with some other one?
Yes, you are correct. I wonder how that happened?
Previous Topic: Connect by clause taking lot of time
Next Topic: difference amount between 2 dates
Goto Forum:
  


Current Time: Thu Mar 28 07:18:00 CDT 2024