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 ).

Questions by swadheengupta   answers by swadheengupta

Showing Answers 1 - 33 of 33 Answers

pranav_suri

  • Jul 15th, 2008
 

-- 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

surymani

  • Oct 11th, 2008
 

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

  Was this answer useful?  Yes

sureshkumar.mtech

  • Oct 18th, 2008
 

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

  Was this answer useful?  Yes

singh13

  • Feb 2nd, 2009
 

update student a set a.maxmarks = (select greatest(marks1,marks2) from student b where a.student_id=b.student_id)


this is working for sure.

  Was this answer useful?  Yes

dj_dj_dj

  • May 29th, 2009
 

Simplest way to do this is update studentset max_marks = (select case when marks1 > marks2 then marks1 else marks2 end). The same can be done using decode function also..

Santhosh R

  • Jul 6th, 2009
 

--Creating a student table
create table student(name varchar(10), marks1 int, marks2 int, maxmarks int)

--Inserting records into student table

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

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

--Updating the third column(maxmarks) depending upin the greatest of marks1 and marks2

update sahv_aa
   set maxmarks =
                  (case when marks1 > marks2

                   then marks1

                   else marks2

                   end)


  Was this answer useful?  Yes

vinu1

  • Dec 8th, 2009
 

UPDATE table_name
SET maxmarks=
CASE
WHEN marks1-marks2>0 THEN marks1
WHEN marks1-marks2<0 THEN marks2
ELSE null
END;

  Was this answer useful?  Yes

pollysingh544

  • Feb 20th, 2010
 

Update student set maxmarks=select marks from

(select marks1 as marks from student where marks1>marks2
                              Union
select marks2 as marks from student where marks2>marks1)

  Was this answer useful?  Yes

sarasmuthu

  • Feb 21st, 2010
 

SQL> select * from test;

        ID         M1         M2       MAXV
---------- ---------- ---------- ----------
         1         10         20
         2         30         29

SQL> update test t set t.maxv = (select case when z.m1 > z.m2 then z.m1 else z.m2 end case from test z where z.id=t.id);

                                             (OR)

SQL> update test set maxv=greatest(m1,m2);


SQL> select * from test;

        ID         M1         M2       MAXV
---------- ---------- ---------- ----------
         1         10         20         20
         2         30         29         30

  Was this answer useful?  Yes

dj_dj_dj

  • Mar 23rd, 2010
 

Student Table:

Id Marks1 Marks2 Max_Marks
1 10 20  
2 20 30  
3 30 10  
4 35 25  
5 20 40  


Desired Querry would be:

UPDATE student
SET max_marks = (SELECT CASE WHEN marks1>marks2
THEN marks1
ELSE marks2
END)

Output:

Id Marks1 Marks2 Max_Marks
1 10 20 20
2 20 30 30
3 30 10 30
4 35 25 35
5 20 40 40

Regards
Dharmendra Jaiswal

  Was this answer useful?  Yes

rk_0907

  • May 2nd, 2010
 

Oracle 9i
-------------
create table marks(name varchar2(10),marks1 number(5),marks2 number(5), maxmarks number(5));
 
insert into marks values ('a',10,20,null);
insert into marks values ('a',20,25,null);
insert into marks values ('a',30,20,null);
commit;

update marks set maxmarks = case when marks1 > marks2 then marks1 else marks2 end
commit;

  Was this answer useful?  Yes

noyaljd

  • May 7th, 2010
 

UPDATE student a
SET maxmarks = (SELECT GREATEST (marks1, marks2)FROM student b
WHERE b.student_id = a.student_id);
COMMIT ;

 

  Was this answer useful?  Yes

Palash Biswas

  • May 20th, 2010
 

Declare @tbl_Test TABLE([Name] Nvarchar(255),Mark1 int,mark2 int,maxmark int)

Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('a',50,20)
Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('b',10,40)
Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('c',80,90)
Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('d',10,50)
Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('e',60,20)
Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('f',40,70)
Insert into @tbl_Test([Name] ,Mark1 ,mark2) VALUES ('g',80,50)

Select * from @tbl_Test

update @tbl_Test Set maxmark = case When mark1 > mark2 then Mark1 eLse mark2 end from @tbl_Test

Select * from @tbl_Test

  Was this answer useful?  Yes

indra.prasad

  • Jul 12th, 2010
 

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 aa
SET maxmarks= CASE WHEN marks1 > marks2 THEN marks1 ELSE marks2 END;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions