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 |
How can we implement rollback or commit statement in a trigger?
What is sql integrity?
Q1.all the depts which has more then 10 empls? Q2.all the dept which does not have any emply? Q3 all the emp which does not have any dept? Q4 get all the emply detais with the dept details it dept is exit otherwise any emp details? Q5 how to debugg the dynamic sql and packages?
Give an example of any procedure.
5 Answers Accenture, iFlex, Wipro,
How to rename a table?
There are 5 records in a table and we have implemented two triggers that are :pre_query and post_query how many times these triggers will fire.
How do we tune the code?
How to perform a loop through all tables in pl/sql?
What is time based sql injection?
What is pl sql block in dbms?
i don't want the message as 14 rows updated just it should update what it will do
what is the difference between $message and $$message? : Sql dba