CODEWITHSUNDEEP
c#mysql
user3442765
user3442765

Reputation: 61

How to update a specific row with 2 distinct IDs?

I have a table wherein I want to update a rows individually:

Transaction ID  EmpID    START    END     LOGDATE
1                 1     8:32:32   NULL   7/25/2016
2                 2     9:02:10   NULL   7/25/2016
3                 3     9:00:56   NULL   7/25/2016
4                 3     9:42:00   NULL   7/26/2016
5                 2     10:58:00  NULL   7/26/2016
6                 1     9:23:00   NULL   7/26/2016

If I use this:

UPDATE EmpLog SET ShiftEnd = '09:00:00' WHERE EmpID = 1 and CONVERT(date,     EmpLog.LogDate) = CONVERT(date, GETDATE())

I can only update the specific row within the day, but since I need to be able to account for overtime, it can't be.

How do I update a specific row to update the END column for a specific transaction with C#?

Basically, the layout of my C# program is that a user must input his EmpID, and press 'START' or 'END'. but the 'END' part is tricky. I ended up updating all rows and losing previous data.

How do I update a specific row with the latest transaction ID for each specific employee? Sorry If i'm confusing.

Upvotes: 0

Views: 48

Answers (1)

smoksnes
smoksnes

Reputation: 10851

I read your question like this:

I want to update the last entry for a specific employee

As long as the transaction id will increase for every entry, you could do something like this:

UPDATE EmpLog 
    SET ShiftEnd = '09:00:00' 
    WHERE EmpID = 1 AND [Transaction ID] = 
(SELECT MAX([Transaction ID]) FROM EmpLog WHERE EmpID = 1)

Read this similiar question:

Is it possible to use MAX in update statement using sql?

This is for MS SQL Server mainly, but I think you can easily translate it to mysql. There may be an even better way in mysql.

Downside with this solution: You have to make an extra select in your update, which will be slower, but for this example if think it should do fine.

Upvotes: 2

Related Questions