Home » SQL & PL/SQL » SQL & PL/SQL » Update table with join (11g R2)
Update table with join [message #575070] Fri, 18 January 2013 22:08 Go to next message
sanushks
Messages: 8
Registered: December 2012
Location: India
Junior Member
Hi all,

I'm unable to get the below update SQL to run in Oracle, it's giving me th below error
ORA-00933: SQL command not properly ended. Any help appreciated.

    UPDATE 
    PDR.PH_Family_Match_by_Chassis a
    SET a.Launched = 'Y'
    INNER JOIN
    PDR.domCHASSIS
    ON
    a.chassis_id = PDR.domCHASSIS.chassis_id
    INNER JOIN PDR.domCHASSIS_MODULE
    ON PDR.domCHASSIS.chassis_id = PDR.domCHASSIS_MODULE.chassis_id
    INNER JOIN
    PDR.domCHASSIS_OPTION
    ON (PDR.domCHASSIS_MODULE.chassis_id = PDR.domCHASSIS_OPTION.chassis_id)
    AND (PDR.domCHASSIS_MODULE.module_id = PDR.domCHASSIS_OPTION.module_id)
    WHERE 
    a.Launched='N' 
    AND PDR.domCHASSIS.current_status_code='A' 
    AND PDR.domCHASSIS_MODULE.current_status_code='A'
    AND PDR.domCHASSIS_OPTION.current_status_code='A'


Re: Update table with join [message #575073 is a reply to message #575070] Fri, 18 January 2013 23:29 Go to previous messageGo to next message
spacebar
Messages: 26
Registered: February 2007
Junior Member
Try it this way:
update pdr.ph_family_match_by_chassis a
  set  a.launched = 'Y'
where  a.launched = 'N'
 and   exists ( select null
                 from  pdr.domchassis pdc join pdr.domchassis_module pdm on pdc.chassis_id           =  pdm.chassis_id
                                                                        and pdm.current_status_code  =  'A'
                                          join pdr.domchassis_option pdo on pdc.chassis_id           =  pdo.chassis_id
                                                                        and pdm.module_id            =  pdo.module_id
                                                                        and pdo.current_status_code  =  'A' )
Re: Update table with join [message #575074 is a reply to message #575073] Fri, 18 January 2013 23:54 Go to previous messageGo to next message
sanushks
Messages: 8
Registered: December 2012
Location: India
Junior Member
Does the logic still hold good with the where conditions in the join?
Re: Update table with join [message #575079 is a reply to message #575074] Sat, 19 January 2013 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Update table with join [message #575103 is a reply to message #575079] Sat, 19 January 2013 06:02 Go to previous messageGo to next message
sanushks
Messages: 8
Registered: December 2012
Location: India
Junior Member
Testing the query provided by spacebar, seems like all the rows on the target table ph_family_match_by_chassis are getting updated. I need only the rows which match the below condition should be updated
PDR.ph_family_match_by_chassis a
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id
Re: Update table with join [message #575104 is a reply to message #575103] Sat, 19 January 2013 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Update table with join [message #575127 is a reply to message #575103] Sat, 19 January 2013 15:27 Go to previous messageGo to next message
spacebar
Messages: 26
Registered: February 2007
Junior Member
sanushks wrote on Sat, 19 January 2013 06:02
Testing the query provided by spacebar, seems like all the rows on the target table ph_family_match_by_chassis are getting updated. I need only the rows which match the below condition should be updated
PDR.ph_family_match_by_chassis a
INNER JOIN
PDR.domCHASSIS
ON
a.chassis_id = PDR.domCHASSIS.chassis_id

My bad, But since we didn't have your table def's and example data I didn't test!! Sad
Updated sql below:
update pdr.ph_family_match_by_chassis a
  set  a.launched = 'Y'
where  a.launched = 'N'
 and   exists ( select null
                 from  pdr.domchassis pdc join pdr.domchassis_module pdm on pdc.chassis_id           =  pdm.chassis_id
                                                                        and pdm.current_status_code  =  'A'
                                          join pdr.domchassis_option pdo on pdc.chassis_id           =  pdo.chassis_id
                                                                        and pdm.module_id            =  pdo.module_id
                                                                        and pdo.current_status_code  =  'A'
                where  pdc.chassis_id  =  a.chassis_id )

Re: Update table with join [message #575129 is a reply to message #575127] Sat, 19 January 2013 19:22 Go to previous messageGo to next message
sanushks
Messages: 8
Registered: December 2012
Location: India
Junior Member
Thanks Spacebar! the updated SQL looks & works fine..
Re: Update table with join [message #575133 is a reply to message #575129] Sun, 20 January 2013 00:45 Go to previous message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe but remember:

Quote:
My bad, But since we didn't have your table def's and example data I didn't test!!


And so

Michel Cadot wrote on Sat, 19 January 2013 13:20
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Previous Topic: Conditional copmilation question
Next Topic: How to tune query?
Goto Forum:
  


Current Time: Sat Apr 19 15:43:18 CDT 2014

Total time taken to generate the page: 0.08626 seconds