从Oracle中的另一个表更新表 [英] Update table from another table in oracle

问题描述

我在数据库中有两个表，首先是以下内容: 第一个表是EMPSHIFT

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

第二张表是SCHEDULEEMPLOYEES

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

我想让PL-SQL存储过程或SQL语句从第二个表更新第一个表数据，以便在特定的列日期中设置班次数据，例如 EmployeeID = 1他在01/04/2017的班次= 7.6

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

推荐答案

或者您在谈论数据透视/取消透视(在Oracle 11g中存在)

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')));

这篇关于从Oracle中的另一个表更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！