Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


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

Write the advantages of partitioning a session?

0 Answers  


Under which circumstances, informatica server creates reject files?

0 Answers   Informatica,


without update strategy how to insert &update?

1 Answers   DELL,


please let me know which type of question arise in interview on project manager round on insurance project with answer pls reply asap.

1 Answers   Accenture,


To import the flat file definition into the designer where should the flat file be placed?

0 Answers   Informatica,






Why do flat file load is faster if you compare that with table load ? Please answer me. Advance Thanks, Manojkumar

11 Answers   IBM,


what is metadata?

6 Answers  


Under what condition selecting sorted input in aggregator may fail the session?

0 Answers  


By using joiner t/r we can join only 2 tables, SO if want join more than 2 flat files ,How can i join?

1 Answers  


What is workflow monitor?

0 Answers  


if we have input eno 1,2,3,1,2,3 year 2001,2002,2003,2001,2002,2003 and sal 10000,20000,30000,20000,30000,40000 ex-output eno totsal 1 30000 2 50000 3 70000

7 Answers  


Data is passed from one active trans and one passive trans into a passive transformation.Is the mapping valid or invalid?

18 Answers   L&T,






Categories