Update a table from another table in oracle 11g

Question

1.00/5 (2 votes)

See more:

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~~

SQL

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

The error is around the from

Posted 27-Jan-17 4:16am

Member 12770648

Updated 11-Sep-20 10:54am

CHill60

v3

Add a Solution

Comments

CHill60 27-Jan-17 11:56am

Well without any idea of what your tables look like, what you are trying to update nor what you have already tried, we're going to find it difficult to help you!

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-01-27T05:58:00

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.

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

SQL

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

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

Member 14936162 · Answer 2 · 2020-09-11T10:55:00

Solution 2

Help me:

SELECT PP.REFPELICULA,PP.DINEROGENERADO, RR.DINERO
FROM PELICULAS PP
  INNER JOIN (SELECT P.REFPELICULA, SUM(R.IMPORTE) DINERO
              FROM PELICULAS P
                INNER JOIN PRESTAMOS R ON P.REFPELICULA = R.REFPELICULA
              GROUP BY P.REFPELICULA
              ) RR ON PP.REFPELICULA = RR.REFPELICULA

update pp.dinerogenerado = rr.dinero ??

Posted 11-Sep-20 10:55am

Member 14936162

Comments

CHill60 21-Sep-20 13:44pm

If you have a question then use the red "Ask a Question" link at the top of the page

Update a table from another table in oracle 11g

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Update a table from another table in oracle 11g

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us