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



In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

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

In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

Answer / srinu

Hi Sanjaygupta1981,

Can you please check the query once :
count(*) or count(hiredate).

Except Count(*) Your query looks good.


If any correctons in my advise please let me
know "srinu.srinuvas@gmail.com"

Is This Answer Correct ?    6 Yes 1 No

In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

Answer / suraj kedia

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

In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

Answer / sanjay gupta

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

In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

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

In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

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

In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

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

In EMP table, for those emp whose Hiredate is same, update their sal by "sal+500" or else..

Answer / sbvp

update emp set sal=sal+500

or
alter table emp modify sal=sal+500

Is This Answer Correct ?    1 Yes 28 No

Post New Answer

More Informatica Interview Questions

what is meant by data driven.. in which scenario we use that..?

2 Answers   IBM,


How do you manage the Parameter files while migrating your data from one environment to another environment?

0 Answers  


why we need informatica

2 Answers   HCL,


what is constraint based loading

2 Answers   IBM,


What is the procedure to load the fact table.Give in detail?

1 Answers  






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

7 Answers   IBM,


yesterday my session run ten min.today its run 30min, wt is the reason? if any issues how to solve that?

2 Answers   iGate,


Which transformation can be created only as reusable transformation but not as non-reusable transformation?

1 Answers   Informatica,


how to get the only updated records in the target

2 Answers  


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.

3 Answers   Wipro,


What is the Rankindex in Ranktransformation?

7 Answers  


Why do we use DSS database for OLAP tools?

2 Answers  


Categories