Click here to Skip to main content
15,063,416 members
Please Sign up or sign in to vote.
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
Updated 11-Sep-20 10:54am
v3
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!

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[^]
   
v2
Comments
Member 12770648 27-Jan-17 23:11pm
   
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
CHill60 28-Jan-17 8:59am
   
I've updated my solution now that I have this information
Member 13546002 20-Sep-20 23:48pm
   
I spent a lot of time writing oracle code versus sql :(
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 ??
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900