OraFAQ Forum: SQL & PL/SQL » update statement using from clause

Home » SQL & PL/SQL » SQL & PL/SQL » update statement using from clause (11.2.0.4, Windows 64-bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update statement using from clause [message #648208]

Thu, 18 February 2016 09:10

MurraySobol
Messages: 5
Registered: February 2016
Location: Kitchener ON

Junior Member

Does Oracle support this form of an UPDATE statement:
update tablea
set column =
(select value
from tableb
where conditions match
)
from tablea
where other conditions

The above is a "generic" version of the script.

Report message to a moderator

Re: update statement using from clause [message #648209 is a reply to message #648208]

Thu, 18 February 2016 09:13

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

MurraySobol wrote on Thu, 18 February 2016 15:10

Does Oracle support this form of an UPDATE statement:
update tablea
set column =
(select value
from tableb
where conditions match
)
from tablea
where other conditions

The above is a "generic" version of the script.

I've highlighted the syntax error.

Report message to a moderator

Re: update statement using from clause [message #648214 is a reply to message #648208]

Thu, 18 February 2016 09:53

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: update statement using from clause [message #648291 is a reply to message #648209]

Fri, 19 February 2016 13:16

MurraySobol
Messages: 5
Registered: February 2016
Location: Kitchener ON

Junior Member

Here is my update statement:

UPDATE s1_vehicle_processing
   SET s1_vehicle_processing.schedule_code =
       (SELECT s1_release.schedule_code
          FROM s1_release,
               s1_contract
         WHERE s1_release.contract_nbr = vp.contract_nbr
           AND s1_release.release_nbr = vp.release_nbr
           AND s1_release.contract_nbr = s1_contract.contract_nbr
           AND s1_contract.pricing_type_code <> 'HLD'
       )
  FROM s1_vehicle_processing vp,
       s1_contract c
 WHERE vp.contract_nbr = c.contract_nbr
   AND c.pricing_type_code <> 'HLD'
   AND vp.schedule_code IS NULL
/

and here is the error I get:

Error starting at line : 1 in command -
UPDATE s1_vehicle_processing
   SET s1_vehicle_processing.schedule_code =
       (SELECT s1_release.schedule_code
          FROM s1_release,
               s1_contract
         WHERE s1_release.contract_nbr = vp.contract_nbr
           AND s1_release.release_nbr = vp.release_nbr
           AND s1_release.contract_nbr = s1_contract.contract_nbr
           AND s1_contract.pricing_type_code <> 'HLD'
       )
  FROM s1_vehicle_processing vp,
       s1_contract c
 WHERE vp.contract_nbr = c.contract_nbr
   AND c.pricing_type_code <> 'HLD'
   AND vp.schedule_code IS NULL
Error at Command Line : 11 Column : 3
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

I don't believe that Oracle supports this type of SQL syntax, at least not in Oracle 11g

Murray

[Edit MC: add and fix code tags]

[Updated on: Fri, 19 February 2016 13:20] by Moderator

Report message to a moderator

Re: update statement using from clause [message #648292 is a reply to message #648291]

Fri, 19 February 2016 13:19

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You will find the syntax Oracle supports in Database SQL Reference.

Michel Cadot wrote on Thu, 18 February 2016 16:53

Please read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: update statement using from clause [message #648294 is a reply to message #648292]

Fri, 19 February 2016 14:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I don't believe that Oracle supports this type of SQL syntax
There are infinite ways to write invalid SQL syntax.
So what is your purpose for posting incorrect SQL?

How will we know when correct answer has been posted?

Report message to a moderator

Re: update statement using from clause [message #648297 is a reply to message #648291]

Fri, 19 February 2016 15:08

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Are you saying that syntax is valid in some other version of SQL? I don't believe you. I've already shown you the clause that is impossible.

Report message to a moderator

Re: update statement using from clause [message #648298 is a reply to message #648297]

Fri, 19 February 2016 15:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

it may be valid for SQL Server

Report message to a moderator

Re: update statement using from clause [message #648299 is a reply to message #648298]

Fri, 19 February 2016 15:32

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Well, perhaps. But it doesn't sense make to apply a FROM clause to an UPDATE statement. Whereas this structure makes perfect sense, and is what OP should be using:

orclz>
orclz> update emp set ename=(select loc from dept where deptno=10) where comm is null;

10 rows updated.

orclz>

Report message to a moderator

Re: update statement using from clause [message #648300 is a reply to message #648299]

Fri, 19 February 2016 16:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.techonthenet.com/sql_server/update.php

I am just the messenger

Report message to a moderator

Re: update statement using from clause [message #648301 is a reply to message #648300]

Fri, 19 February 2016 16:17

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

What horrible syntax that third one is. I hope it isn't part of the ANSI standard.

Well, that's all from me for a while. I'm off to Lindbergh Field shortly. Goodbye, Del Mar! As close to paradise as one is likely to get.

Report message to a moderator

Re: update statement using from clause [message #648386 is a reply to message #648301]

Mon, 22 February 2016 14:57

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Try the following

UPDATE S1_vehicle_processing Vp
   SET S1_vehicle_processing.Schedule_code =
          (SELECT S1_release.Schedule_code
             FROM S1_release, S1_contract
            WHERE     S1_release.Contract_nbr = Vp.Contract_nbr
                  AND S1_release.Release_nbr = Vp.Release_nbr
                  AND S1_release.Contract_nbr = S1_contract.Contract_nbr
                  AND S1_contract.Pricing_type_code <> 'HLD')
 WHERE     EXISTS
              (SELECT NULL
                 FROM S1_contract C
                WHERE     Vp.Contract_nbr = C.Contract_nbr
                      AND C.Pricing_type_code <> 'HLD')
       AND Vp.Schedule_code IS NULL;

Report message to a moderator

Re: update statement using from clause [message #648488 is a reply to message #648386]

Wed, 24 February 2016 01:38

varlamovvp
Messages: 13
Registered: February 2016
Location: Russia

Junior Member

Hi MurraySobol!
May be you should read about updatable views in Oracle.
Additionally I recommend you MERGE statement. It is very powerful.

Report message to a moderator

Re: update statement using from clause [message #648517 is a reply to message #648488]

Wed, 24 February 2016 08:24

MurraySobol
Messages: 5
Registered: February 2016
Location: Kitchener ON

Junior Member

We had been trying to using the MERGE statement but where having difficulty with it.
Another poster provided a solution using a modified form of an UPDATE statement so we will use that.
Thanks
Murray

Report message to a moderator

Re: update statement using from clause [message #648550 is a reply to message #648517]

Thu, 25 February 2016 04:31

varlamovvp
Messages: 13
Registered: February 2016
Location: Russia

Junior Member

Hi! I'm not sure, but maybe it will help you

MERGE into S1_vehicle_processing tu
USING (
  SELECT r.schedule_code, vp.rowid as rwd
  FROM s1_release r, s1_contract c, s1_vehicle_processing vp
         WHERE r.contract_nbr = vp.contract_nbr
           AND r.release_nbr = vp.release_nbr
           AND r.contract_nbr = c.contract_nbr
           AND vp.contract_nbr = c.contract_nbr
           AND c.pricing_type_code <> 'HLD'
           AND vp.schedule_code IS NULL
) t
ON
( tu.rowid=t.rwd )
WHEN MATCHED THEN UPDATE SET tu.schedule_code=t.schedule_code
  ;

Report message to a moderator

Previous Topic:	External table error
Next Topic:	Query to find all records with either same value or null value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 02:25:35 CDT 2024