SQL Question

UPDATE or INSERT INTO is not working in procedure?

Table

Attendance
:

EMPL_KODE |EMPL_NAME |DATE_IN |TIME_IN |TIME_OUT|TOTAL_MIN |TOTAL_HOUR
--------------------------------------------------------------------------
001 | Michel |25.04.2016 |06:50 |15:40 |NULL |NULL
002 | Clara |25.04.2016 |06:15 |15:43 |NULL |NULL
003 | Rafael |25.04.2016 |06:25 |15:45 |NULL |NULL
001 | Michel |26.04.2016 |06:23 |15:42 |NULL |NULL
002 | Clara |26.04.2016 |06:10 |15:41 |NULL |NULL
003 | Rafael |26.04.2016 |06:30 |15:42 |NULL |NULL
001 | Michel |27.04.2016 |06:33 |15:42 |NULL |NULL
002 | Clara |27.04.2016 |06:54 |15:44 |NULL |NULL
003 | Rafael |27.04.2016 |07:00 |15:45 |NULL |NULL


I've success to create procedure to insert value automatically for column
TOTAL_MIN
and
TOTAL_HOUR
with this syntax :

SET TERM ^ ;
ALTER PROCEDURE UPDATEEMPLOYEES
AS
DECLARE VARIABLE EMPL_KODE CHAR(5);
DECLARE VARIABLE EMPL_NAME VARCHAR (25);
DECLARE VARIABLE TIME_IN TIMESTAMP;
DECLARE VARIABLE TIME_OUT TIMESTAMP;
DECLARE VARIABLE TOTAL_MINUTES INTEGER;
DECLARE VARIABLE TOTAL_HOURS FLOAT;

BEGIN
FOR SELECT e.EMPL_KODE, e.EMPL_NAME,
CAST(a.DATE_IN + a.TIME_IN AS TIMESTAMP),
CAST(a.DATE_IN + a.TIME_OUT AS TIMESTAMP),
DATEDIFF(MINUTE,a.TIME_IN,a.TIME_OUT),
DATEDIFF(HOUR,a.TIME_IN,a.TIME_OUT)
FROM EMPLOYEE e
JOIN ATTENDANCE a
ON e.EMPL_KODE=a.EMPL_KODE

INTO :EMPL_KODE,:EMPL_NAME,:TIME_IN,:TIME_OUT,TOTAL_MINUTES,TOTAL_HOURS
DO
UPDATE ATTENDANCE a
set a.TOTAL_MINUTES=:TOTAL_MINUTES,
a.TOTAL_HOURS=:TOTAL_HOURS
WHERE a.EMPL_KODE=:EMPL_KODE;

end^
SET TERM ; ^


But, the problem is how can I write the syntax directly with
UPDATE
or
INSERT INTO
? I've tried but still not working.

I write the code same as the Firebird instruction.

UPDATE OR INSERT INTO
{tablename | viewname} [(<columns>)]
VALUES (<values>)
[MATCHING (<columns>)]
[RETURNING <values> [INTO <variables>]]

<columns> ::= colname [, colname ...]
<values> ::= value [, value ...]
<variables> ::= :varname [, :varname ...]


Here is my code:

I'm not write the
Returning
because I don't understand what it is.

SET TERM ^ ;
ALTER PROCEDURE BLABLA(
TOTAL_M INTEGER,
TOTAL_H FLOAT)
AS
BEGIN
UPDATE or INSERT INTO ATTENDANCE (TOTAL_MINUTES,TOTAL_HOURS)
VALUES (:TOTAL_M,:TOTAL_H)
MATCHING (EMPL_KODE);
end^
SET TERM ; ^


After I commit the statement, it shows the error message :


UPDATE OR INSERT field list does not match MATCHING clause


What should I do ?

Answer

Finally, I've got the answer from this http://www.firebirdfaq.org/faq336

SET TERM ^ ;
ALTER PROCEDURE UPDATEEMPLOYEEES
AS
begin
UPDATE ATTANDENCE
SET TOTAL_MINUTES=DATEDIFF(MINUTE,TIME_IN,TIME_OUT),
    TOTAL_HOURS=DATEDIFF(MINUTE,TIME_IN,TIME_OUT)/60.0;
END^
SET TERM ; ^