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
- 8 Answers
- 11857 Views
- Cap Gemini, I also Faced
- E-Mail Answers
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 are the challenges you have faced in your Project (DWH- ETL)? Explain with example?
There are 4 flat files with number of records as indicated below. Which files should be picked first for joining using joiners so as to get best performance. File A - 1000 records File B - 100 records File c - 10000 records File D - 10 records Please explain. Thanks and Regards,
what is data driven?
Suppose we have a source qualifier transformation that populates two target tables. How do you ensure tgt2 is loaded after tgt1?
what is bitmap index? did u use it?and how to use it in informatica
What is the difference between router and filter?
Debugger what are the modules, what are the options you can specify when using debugger, can you change the expression condition dynamically when the debugger is running.
what happens when a batch fails?
how you will maintain version?
What are the different types of code pages available in informatica & how to implement it in your project?
Can a joiner be used in a mapplet.
Enlist the tasks for which source qualifier transformation is used.
Teradata 
