Handling duplicates while updating records in SQL using where clause

Question

I have a situation where i need to update a row in table and when faced with a duplicate entry then take a decision based on another column value.

For example let's say my table is like this : salary_table

 Salary    Username   Usersurname  Last_entry_date

  3000      abc         bak             20-feb-13

  4000      sdf         kup             20-mar-15

  5000      abc         bak             20-mar-15

so my update query is something like this

update salary_table 
set salary=9000 
where username=abc 
  and usersurname=bak;

For records like row 2 when there is unique entry this will not cause any problem

but i will get multiple rows for records like 1 (1 and 3) but i only want to update one row. In this case i would like to check last_entry_date. And the entry which has latest date (row 3 in this case) should get updated.

How can it be done ?

Any help would be highly appreciated.

mucio · Accepted Answer · 2015-04-24 16:46:51Z

1

Update salary_table
set salary = 9000
where username= 'abc'
  and usersurname= 'bak'
  and Last_entry_date = (select max(Last_entry_date)
                        from SalaryTable
                        where s.username = username
                        and s.usersurname = usersurname);

answered Apr 24, 2015 at 16:46

mucio

7,1271 gold badge23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ramki · Accepted Answer · 2015-04-24 16:45:02Z

0

you have to add "where clause" on what you want in this case "last_entry_date = ??" With out adding proper filter how you identify which row to be updated.

answered Apr 24, 2015 at 16:45

Ramki

4832 silver badges8 bronze badges

1 Comment

Ramki Over a year ago

hi , i mean how you update if 3 or more rows are matching ... you should have correct application logic to update which row on what criteria.

Collectives™ on Stack Overflow

Handling duplicates while updating records in SQL using where clause

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related