0

I have two tables in Database first is the following : First Table is EMPSHIFT

EmployeeID   01/04/2017   02/04/2017    03/04/2017   04/04/2017   to  30/04/2017
--------------------------------------------------------------------------------
1              7,6
2
3
4

to end of all employees

second table is SCHEDULEEMPLOYEES

EmployeeID    DayDate           Shift
------------------------------------
1            01/04/2017          7,6
1            02/04/2017          5,2
1            03/04/2017          7,6
1            04/04/2017          9
2            01/04/2017          9
2            02/04/2017          3,2
3            01/04/2017          7,6
3            03/04/2017          9

to the end of table data

I want make PL-SQL stored procedure or SQL statement to update the first table data from the second as to set the shift data in the specific column date like for EmployeeID = 1 his shift in 01/04/2017 = 7,6

what i have tried till now is the following What I tried till now

but doesn't work can any help in this

Mohamed Hashem
  • 21
  • 2
  • Can you please post your code here for review. Links are not accessible to all. – XING May 10 '17 at 10:07
  • 1
    You apply updates using the [`UPDATE`](https://docs.oracle.com/database/121/SQLRF/statements_10008.htm) or [`MERGE`](https://docs.oracle.com/database/121/SQLRF/statements_9017.htm) statements. What isn't working? – William Robertson May 10 '17 at 10:26
  • 1
    Possible duplicate of [Oracle SQL: Update a table with data from another table](http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table) – Alexandre Neukirchen May 10 '17 at 10:27

2 Answers2

0

In this case you should use MERGE statement as below:

DECLARE
  l_date DATE:=TO_DATE('01/04/2017','DD/MM/YYYY');
BEGIN
  LOOP
    EXECUTE IMMEDIATE 'MERGE INTO EMPSHIFT E
USING SCHEDULEEMPLOYEES S
ON (S.EMPLOYEEID=E.EMPLOYEEID AND S.DAYDATE=TO_DATE('''||TO_CHAR(l_date)||''',''DD/MM/YYYY''))
WHEN MATCHED 
THEN UPDATE SET E."'||TO_CHAR(l_date)||'"=S.SHIFT';
    l_date:=l_date+1;
    EXIT
  WHEN l_date>TO_DATE('30/04/2017','DD/MM/YYYY');
  END LOOP;
END;

You mentioned that you want to update records, but if you would like to insert missing records (for missing EMPLOYEEID in EMPSHIFT table), please refer to merge_insert_clause

m3rgus
  • 39
  • 5
0

or you talking about pivot/unpivot (it's exist in Oracle 11g)

SELECT *
  FROM (SELECT DayDate,
               EmployeeID,
               SUM(Shift) shift
          FROM SCHEDULEEMPLOYEES
         GROUP BY DayDate,
                  EmployeeID)
PIVOT(SUM(shift)
   FOR DayDate IN(to_date('01/04/2017',
                          'mm/dd/yyyy'),
                  to_date('02/04/2017',
                          'mm/dd/yyyy'),
                  to_date('03/04/2017',
                          'mm/dd/yyyy'),
                  to_date('04/04/2017',
                          'mm/dd/yyyy')));
Leo
  • 519
  • 2
  • 10