0

Good day, I have a problem regarding MySQL queries. I want to create a daily time record output but my problem is on making queries. What I want is that when a record have the same id and date it must be updated otherwise it will insert a new record. These are some of screenshots of table properties

enter image description here

enter image description here

arreojn
  • 43
  • 1
  • 1
  • 4
  • 1
    I had the same issue before, my solution is to run a select query, if it returns a row, update it, otherwise, it will insert a new one. Have you tried that approach? – Ruben_PH Oct 12 '17 at 06:15
  • [Insert into a mysql table or update if exists](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) – Ruben_PH Oct 12 '17 at 06:21
  • I tried that approach but I want to learn it using sql. Thanks for the suggestion btw – arreojn Oct 12 '17 at 06:30
  • Try the link I provided above – Ruben_PH Oct 12 '17 at 06:34
  • Possible duplicate of [Insert into a MySQL table or update if exists](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) – Ruben_PH Oct 12 '17 at 06:35

1 Answers1

0
    CREATE PROCEDURE dbo.EditGrade
        @Id int 
       ,@TimeIn datetime
       ,@TimeOut datetime
       ,@Existing bit OUTPUT
    AS
    BEGIN

        SET NOCOUNT ON;

        DECLARE @CurrentTimeIn as datetime
DECLARE @CurrentId as int


        SELECT  @CurrentId = Id                 
        FROM    tblAttendance
        WHERE   TimeIn = @TimeIn

        IF (@CurrentId <> @Id)     
           BEGIN
            IF (SELECT COUNT(ISNULL(Id,0))
                FROM tblAttendance
                WHERE TimeIn = @TimeIn            
                    SET @Existing = 0
                ELSE 
                    SET @Existing = 1
            END
        ELSE
           BEGIN
                SET @Existing = 0
           END

           IF @Name = ''
            SET @Name = null

        IF (@Existing = 0)
            UPDATE tblAttendance
            SET TimeIn = @TimeIn
              --other column values here
            WHERE Id = @Id
        ELSE
            --INSERT FROM tblAttendance query here 

    END


    GO

this is from stored procedure of ms sql, you can just convert it into mysql version.

take note, datetime types also checks the seconds, so don't include the seconds as much as possible or it will render as NOT THE SAME (e.g time in = 10:00:01 and time out is 10:00:02 will be rendered as NOT THE SAME)

NoobProgger
  • 70
  • 1
  • 9