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
- 12001 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 do you mean by blocking transformation?
can anyone suggest best free Talend data integration training online
How identifying bottlenecks in various components of informatica and resolving them?
i have a table like empid ename year month sal 1 x 98 jan 500 1 x 98 feb 500 1 x 98 mar 500 1 x 99 jan 600 1 x 99 feb 600 2 y 98 jan 600 2 y 98 feb 600 2 y 98 mar 600 2 y 99 jan 700 2 y 99 jan 700 and so on i want to find out totsal for every emp on year wise plz help me
difference between top down(w.h inmon)and bottom up(ralph kimball)approach?
My source is flat file which contain only one column with data type varchar.now i want to send string data types into one target and if any numbers and special characters are there that should be send it into another target.so how do you design a mapping for this?
How do you load only null records into target? Explain through mapping flow.
What is up date strategy and what are the options for update strategy?
Differentiate between source qualifier and filter transformation?
How can i generate Seqence Numbers to Tagret Table (with out using Seqence Gen Trans,Rank Trans).
What is a stored procedure transformation?
How to merge First Name & Last Name?
Teradata 
