0

I have a table PersonalInfo having columns such as Id, Name, Address.

Suppose I have following data

1    John    US
2    Mark    UK
3    John    UK
4    David   US

Now when I insert following new record

5 John China

I want to update the last record having same name as the new one for example as shown here the record 3 John UK will be updated to 3 John China.

When I insert the 5th record the table should be 

1    John    US
2    Mark    UK
3    John    China
4    David   US
5    John    China

what sql query should I use?

Twix
  • 392
  • 1
  • 12
  • 38
  • Whenever insert new record I want to update previous record with same name but only the last one which matches – Twix Oct 17 '14 at 10:42
  • see http://stackoverflow.com/questions/20942043/update-with-the-latest-date – AjV Jsy Oct 17 '14 at 10:44
  • Please, elaborate your question. Which is the final estate of the table when you insert 5 John China? – JotaBe Oct 17 '14 at 10:57

1 Answers1

0

Assuming the Id is auto-incremented, it would be the highest value for that Name and Address combination.

UPDATE PersonalInfo
SET Address = 'US'
WHERE ID = (SELECT MAX(Id)
FROM PersonalInfo
WHERE Name = 'John'
AND Address = 'UK'
)
Vinnie
  • 3,889
  • 1
  • 26
  • 29
  • Please update your nearly correct answer: remove the Address from the WHERE predicate. Just take the last record from John, whatever the address is, and replace it. – JotaBe Oct 17 '14 at 10:54
  • after insert the max Id will be 5 but I want to update second max Id – Twix Oct 17 '14 at 10:55
  • @Twix do you want all records to be updated to have the new address for a give inserted name? For example, when 'John','China' gets inserted, do you want Id 3 and 1 to also be updated to have 'China' as the address or just Id = 3? – Vinnie Oct 17 '14 at 10:58
  • 1
    Do the `UPDATE` before the insert, or add to the `WHERE` clause to exclude the new row. You could add `AND Id != (SELECT MAX(Id) FROM PersonalInfo)` to the `WHERE` clause (the second one). – AjV Jsy Oct 17 '14 at 11:41