how to Update table Sales_summary with max(sales) data from
table sales_dataTable 1. sales_data table Table 2.
Sales_summary
Region sales Region sales
N 500 N 0
N 800 W 0
N 600
W 899
W 458
W 900
I want the Sales_summary After Update like this
Region Sales
N 800
W 900
Answers were Sorted based on User's Feedback
Answer / soma
Update Sales_summary set sales = s2.sales
From Sales_summary s1,
(
Select Region, max(sales) sales
From sales_data group by Region
)s2
where s1.Region = s2.Region;
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / krishna
SQL> desc a;
Name Null? Type
------------------------------- -------- ----
A VARCHAR2(2)
B NUMBER(3)
SQL> select * from a;
A B
-- ---------
N 500
N 800
N 600
W 899
W 458
W 900
6 rows selected.
SQL> desc b;
Name Null? Type
------------------------------- -------- ----
A VARCHAR2(2)
B NUMBER(3)
SQL> select * from b;
no rows selected
SQL> insert into b (select a, max(b) from a group by a);
2 rows created.
SQL> select * from b;
A B
-- ---------
N 800
W 900
SQL>
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / sunil
-- It can be done by simple procedure
DELIMITER $$
drop procedure if exists updatesale$$
create procedure updatesale()
BEGIN
declare l_loop_end INT default 0;
declare l_region varchar(10);
declare l_sale int ;
declare cur_1 cursor for select region 'Region',max(sales)
'SALES' from sales_data group by region;
declare continue handler for sqlstate '02000' set l_loop_end
= 1;
open cur_1;
repeat
fetch cur_1 into l_region,l_sale;
if not l_loop_end then
update sales_summery set sales=l_sale where region=l_region;
end if;
until l_loop_end end repeat;
close cur_1;
end$$
DELIMITER ;
call updatesale;
-- now check
select * From sales_summery;
region sales
w 900
N 800
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / rm
The question is to Fetch max(sales) from sales_data
and with this value update the sales column
in the sales_summary table.
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / keerthi
update Sales_summary
set Sales=(select max(Sales)from Sales_data where
Region=®ion) where Region=®ion;
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / vipul garg
Update Sales_summary s1 set sales =
(
Select max(sales) sales
From sales_data s2
where s1.Region = s2.Region
);
Is This Answer Correct ? | 0 Yes | 1 No |
What is the result, when NULL is compared with NULL?
What is a bitmap index?
4 Answers Choice Solutions, Infosys,
How is pl sql different from sql?
What is the use of a view in sql?
how many triggers are allowed in mysql table? : Sql dba
What is the max nvarchar size?
If there are 1 to 100 numbers in a table and in that 100 numbers some 10 numbers are deleted.I want to find out the missing numbers between 1 to 100 by pl/sql how?
What is the reports view in oracle sql developer?
What is oracle sql developer?
what is recursive stored procedure? : Sql dba
What are the advantages of normalization?
Is left join faster than join?