In EMP table, for those emp whose Hiredate is same, update
their sal by "sal+500" or else for others keep the sal as it
is, how to do it by SQL query
Answers were Sorted based on User's Feedback
Answer / sanjaygupta1981
UPDATE emp
SET sal=sal+500
WHERE hiredate IN (SELECT hiredate
FROM employees
HAVING COUNT(*)>1
GROUP BY hiredate)
The above query will update the records of all those employees
whose hiredate is same.
Is This Answer Correct ? | 17 Yes | 5 No |
Hi Sanjay,
the query u posted is a bit wrong,1st u have to do group by
n then having clause comes n not vise versa. so the final
query will be as below:
UPDATE emp
SET sal=sal+500
WHERE hiredate IN (SELECT hiredate
FROM employees
GROUP BY hiredate
HAVING COUNT(hiredate)>1
)
Hi Srinu,
It doesn't matter whether u write count(*) or
count(hiredate),the answer will always the same but its
better to give count(hiredate) only as it is easier to
understand.
Is This Answer Correct ? | 6 Yes | 2 No |
Hi Srinu,
you check the query once again..Query is not wrong..
UPDATE emp
SET sal=sal+500
WHERE hiredate IN (SELECT hiredate
FROM emp
HAVING COUNT(*)>1
GROUP BY hiredate
it'll give the same result whether we write count(*) or
count(hiredate)..
But I agree but its better to give count(hiredate)instead of
COUNT(*)...coz it is easier to understand.
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / guest
update emp a set a.sal=a.sal+500 where a.hiredate in(select
max(b.hiredate) from emp b group by b.hiredate having
count(b.hiredate)>1);
or
update emp a set a.sal=a.sal+500 where a.hiredate=(select
max(b.hiredate) from emp b where a.hiredate=b.hiredate group
by b.hiredate having count(b.hiredate)>1);
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sukanta
update emp a set a.sal=a.sal+500 where a.hiredate in(select
max(b.hiredate) from emp b group by b.hiredate having
count(b.hiredate)>1);
or
update emp a set a.sal=a.sal+500 where a.hiredate=(select
max(b.hiredate) from emp b where a.hiredate=b.hiredate group
by b.hiredate having count(b.hiredate)>1);
above is Posted By
Sukanta
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / srinivas
Hi suraj no need to put first group by and then having
clause
we can give any order it will work
please check and let me know
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / sbvp
update emp set sal=sal+500
or
alter table emp modify sal=sal+500
Is This Answer Correct ? | 1 Yes | 28 No |
what is meant by data driven.. in which scenario we use that..?
How do you manage the Parameter files while migrating your data from one environment to another environment?
why we need informatica
what is constraint based loading
What is the procedure to load the fact table.Give in detail?
There is a table with emp salary column how to get the fields belongs to the salary greater than the average salary of particular department. Write a query
yesterday my session run ten min.today its run 30min, wt is the reason? if any issues how to solve that?
Which transformation can be created only as reusable transformation but not as non-reusable transformation?
how to get the only updated records in the target
i have f;latfile source. i have two targets t1,t2. i want to load the odd no.of records into t1 and even no.of recordds into t2.
What is the Rankindex in Ranktransformation?
Why do we use DSS database for OLAP tools?