query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2011-03-27 : 04:53:57

IN A TABLE I HAVE

EMPID LEAVEID REMARKS

1 12 FBC
1 12 FBC
1 12 FBC
2 12 FBC
2 11 BC
2 11 BC
2 12 FBC
2 10 C
2 12 FBC

FOR EACH EMPLOYEE IF LEAVE ID IS 12 THREE TIME I WANT TO UPDATE ONE OF ID TO 1 AND REMARKS TO ab FOR THAT EMPLOYEE

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-27 : 07:31:21

Two questions:

Do you care which one of the three you change from FBC to AB? If you do, you will some way to order the rows. For example, is there a date column or a serial number? If so we could use that.

What do you want to do if there are more than three rows with leaveid = 12 for the same EMPID? Do you want to update all except two?

Assuming you don't care about which one(s) you update, and you want to update all except two, here is one way of doing it:

with a as
(
   select 
      *,
      row_number() over (partition by empid, leaveid order by (select null)) as rowId
   from
      YourTable
   where
      leaveId = 12
)
update a set remarks = 'ab', leaveid = 1
where rowId >= 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources