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

问题描述

需要从anothothe表更新表格



所有我已经尝试过的例子都没有工作。



需要一个很好的例子参考网站



我尝试过的事情：



我所得到的所有例子都没有工作

Need to update a table from anothothe table

All the examples I have gotten and tried just woont work.

Need a reference site with a good example

What I have tried:

All the examples I have gotten and tried just woont work

UPDATE HL
 SET HL.ALLOW8 = H.ALLOW8 ,
 HL.ALLOW9 = H.ALLOW9 ,
 HL.ALLOW10 = H.ALLOW10 
 FROM PAYSLIP HL
 INNER JOIN PAYROLL H
 ON H.IDNO=HL.IDNO 
 WHERE HL.YEAR1 ='2017' AND H.YEAR1 ='2017'

错误消息

error message

ORA -00933 SQL command not properly ended

错误在来自

推荐答案

以下是您想要做的几个参考：

< a href =https://www.google.co.uk/?gfe_rd=cr&ei=rnuLWJ-iBYLW8AeBwKnIBA&gws_rd=ssl#q=oracle+update+table+from+another+table> Google [ ^ ]

如果您无法解决问题，请务必使用您尝试过的代码并更好地描述问题比不工作 - 例如实际的错误信息或实际结果。



编辑：

与SQL Server不同，Oracle不支持更新的JOIN语法。 br />
您需要执行以下操作：

Here are several references for what you want to do:
Google[^]
If you are unable to progress your problem then by all means come back with the code you have tried and a better description of the problem than "woont work" - e.g. the actual error message or the actual results.


Unlike SQL Server, Oracle does not support the JOIN syntax for updates.
You need to do something like the following:

UPDATE PAYSLIP HL
 SET (ALLOW8, ALLOW9, ALLOW10) = 
   (
       SELECT ALLOW8, ALLOW9, ALLOW10
       FROM PAYROLL H WHERE H.IDNO=HL.IDNO  AND H.YEAR1 ='2017'
    )
 WHERE EXISTS (
    SELECT 1 FROM PAYROLL H
     WHERE H.IDNO=HL.IDNO  AND H.YEAR1 ='2017')
AND HL.YEAR1 ='2017';

如果要更新多个列，使用 MERGE [ ^ ]。更多示例 - ORACLE-BASE - MERGE Statement [ ^ ]

If you are updating several columns it's much more efficient to use MERGE[^]. Further examples - ORACLE-BASE - MERGE Statement[^]

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