SQL - write a query to update third column such that

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 167 of 171

write a query to update third column such that

There is a table having the following columns :-
student id marks1 marks2 maxmarks
1 10 20 20
2 25 30 30
3 30 10 30
4 35 25 35
5 20 40 40

write a query to update column maxmarks such that maxmarks column contains
whatever be the greater value among marks1 and marks2 columns ( as shown in table ).

Total Answers and Comments: 21

Last Update: October 28, 2009 Asked by: swadheengupta

Sponsored Links

Best Rated Answer

—

Submitted by: pranav_suri

-- SQL SERVER 2000

create table aa(name varchar(10), marks1 int, marks2 int, maxmarks int)

insert into aa values ('a',10,20,null)

insert into aa values ('b',20,25,null)

update a
set maxmarks= d.marks
from aa a, (select name, max(marks) as marks
                  from (select name,marks1 as marks from aa
                           union all
                           select name,marks2 as marks from aa) as c
                  group by name ) as d
where a.name=d.name

Above answer was rated as good by the following members:
spohilko, crc3459, sureshkumar.mtech

Sorting Options

Page 1 of 3

« First

Last »

July 15, 2008 09:04:15

grmadhankumar

Member Since: June 2006 Contribution: 1

RE: write a query to update third column such that

update table student set maxmarks decode(sign(mark1-mark2) -1 mark1 mark2);

Is this answer useful? Yes | No

0 2

Overall Rating: -2

July 15, 2008 10:53:48

guggulla

Member Since: July 2008 Contribution: 1

RE: write a query to update third column such that

update table student column maxmarks where marks2 max(marks1 marks2);

Is this answer useful? Yes | No

July 15, 2008 18:07:34

pranav_suri

Member Since: July 2008 Contribution: 1

RE: write a query to update third column such that

-- SQL SERVER 2000

create table aa(name varchar(10) marks1 int marks2 int maxmarks int)

insert into aa values ('a' 10 20 null)

insert into aa values ('b' 20 25 null)

update a
set maxmarks d.marks
from aa a (select name max(marks) as marks
from (select name marks1 as marks from aa
union all
select name marks2 as marks from aa) as c
group by name ) as d
where a.name d.name

Is this answer useful? Yes | No

1 0

Overall Rating: +1

July 29, 2008 17:15:25

luttappi

Member Since: June 2008 Contribution: 3

RE: write a query to update third column such that

SQL> update student set maxmarks greatest(m1 m2);

I took 'student' as the table name.

Is this answer useful? Yes | No

1 3

Overall Rating: -2

September 09, 2008 04:54:29

tripathi.atul

Member Since: September 2008 Contribution: 4

RE: write a query to update third column such that

Instead of -1 positive 1 should be there in the decode.

Another way can be to use the greatest sql function.

update aa set maxmarks greatest(marks1 marks2)

Is this answer useful? Yes | No

1 0

Overall Rating: +1

October 11, 2008 02:04:48

surymani

Member Since: October 2008 Contribution: 1

RE: write a query to update third column such that

create table aa(name varchar(10) marks1 int marks2 int maxmarks int)

insert into aa values ('a' 10 20 null)

insert into aa values ('b' 20 25 null)

update a
set maxmarks d.marks
from aa a (select name max(marks) as marks
from (select name marks1 as marks from aa
union all
select name marks2 as marks from aa) as c
group by name ) as d
where a.name d.name

Is this answer useful? Yes | No

October 18, 2008 10:56:06

sureshkumar.mtech

Member Since: May 2008 Contribution: 37

RE: write a query to update third column such that

table have a data:
-----------------

SQL> select * from sa;

NAME SALA SALARY MAX_SALARY
---------- ---------- ---------- ----------
sathish 1000 2000
saro 2500 1250
mathu 7000 5000
karthi 1700 900

Result:
--------

update sa set max_salary
(
select x.Max_salary1 from
(
select name max(sal) Max_Salary1 from
(
select name sala sal from sa
union all
select name salary sal from sa)
group by name)x where sa.name x.name
);

SQL> select * from sa;

NAME SALA SALARY MAX_SALARY
---------- ---------- ---------- ----------
sathish 1000 2000 2000
saro 2500 1250 2500
mathu 7000 5000 7000
karthi 1700 900 1700