Home » SQL & PL/SQL » SQL & PL/SQL » Update Query (merged)
Update Query (merged) [message #350956] Sun, 28 September 2008 03:53 Go to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
Please help me know why this update query is not running ?
 
UPDATE sfd_Locations
SET    sfd_Locations.Govt_Code = Temp_Vill_Code.Govt_Code,
       sfd_Locations.Mark_Code = Temp_Vill_Code.Mark_Code,
       sfd_Locations.vIll_Code = Substr(Temp_Vill_Code.Vill_Code,1,2)
WHERE  sfd_Locations.Serial_Number = Temp_Vill_Code.Serial_Number
       AND sfd_Locations.Loc_Type = 'VIL';


Thanks...
Rania
Re: Update Query (merged) [message #350958 is a reply to message #350956] Sun, 28 September 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please let us know what is the error.

Regards
Michel
Re: Update Query (merged) [message #350959 is a reply to message #350958] Sun, 28 September 2008 04:08 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
This is the error message I get :


ORA-00904: "TEMP_VILL_CODE"."SERIAL_NUMBER": invalid identifier


Purpose of the this query :
is to update the empty fields ,Govt_Code,Mark_Code & vIll_Code in table sfd_locations
to have the data from table Temp_Vill_Code



[Updated on: Sun, 28 September 2008 04:12]

Report message to a moderator

Re: Update Query (merged) [message #350961 is a reply to message #350959] Sun, 28 September 2008 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at MERGE statement.

Regards
Michel
Re: Update Query (merged) [message #350962 is a reply to message #350961] Sun, 28 September 2008 05:46 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
Michel Cadot wrote on Sun, 28 September 2008 11:43
Have a look at MERGE statement.



Thanks for the beneficial article...
since its my purpose to update records from table to another.

I did make use the statement, but I got another error
MERGE INTO sfd_Locations D
   USING Temp_Vill_Code  S
   ON (S.Serial_Number = D.Serial_Number)
   WHEN MATCHED
   THEN UPDATE
     SET D.Govt_Code = S.Govt_Code,
         D.Mark_Code = S.Mark_Code,
         D.Vill_Code = S.Vill_Code;
         



Error message : ORA-00905: missing keyword


Please advise.
Many thanks,
Rania
Re: Update Query (merged) [message #350963 is a reply to message #350962] Sun, 28 September 2008 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must always post your version (with 4 decimals).
In 9i, if this is your version, you have to also put a WHEN NOT MATCHED clause even if this never happens.

Regards
Michel

[Updated on: Sun, 28 September 2008 06:08]

Report message to a moderator

Re: Update Query (merged) [message #350964 is a reply to message #350963] Sun, 28 September 2008 06:14 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
The following code issues an error:

MERGE INTO sfd_Locations D
USING Temp_Vill_Code S
ON (S.Serial_Number = D.Serial_Number)
WHEN MATCHED
THEN UPDATE SET D.Govt_Code = S.Govt_Code, D.Mark_Code = S.Mark_Code, D.Vill_Code = S.Vill_Code
WHEN NOT MATCHED
THEN INSERT (D.serial_number, D.vill_name)
VALUES (S.serial_number, S.vill_name)
WHERE (S.serial_number >= 8000);



ORA-00933: SQL command not properly ended
Re: Update Query (merged) [message #350965 is a reply to message #350964] Sun, 28 September 2008 06:16 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
Our DB version is 9.0.1
Re: Update Query (merged) [message #350966 is a reply to message #350965] Sun, 28 September 2008 06:18 Go to previous messageGo to next message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
MERGE INTO sfd_Locations D
   USING Temp_Vill_Code  S
   ON (S.Serial_Number = D.Serial_Number)
   WHEN MATCHED
   THEN UPDATE SET D.Govt_Code = S.Govt_Code, D.Mark_Code = S.Mark_Code, D.Vill_Code = S.Vill_Code
   WHEN NOT MATCHED
   THEN INSERT (D.serial_number, D.vill_name)
   VALUES (S.serial_number, S.vill_name)
   WHERE (S.serial_number >= 8000);

Re: Update Query (merged) [message #350972 is a reply to message #350966] Sun, 28 September 2008 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
WHERE (S.serial_number >= 8000);

This does not exist in the WHEN NOT MATCHED part in version 9.0.
Please refer to your version SQL Reference.

Regards
Michel
Re: Update Query (merged) [message #351070 is a reply to message #350972] Mon, 29 September 2008 03:22 Go to previous message
raniams
Messages: 22
Registered: September 2008
Location: Egypt
Junior Member
Thanks for your Support. The query successfully did the update.

My best regards...
Previous Topic: Merge Join Cartesian
Next Topic: oracle sql,pl/sql with interfaces
Goto Forum:
  


Current Time: Sun Dec 11 00:09:12 CST 2016

Total time taken to generate the page: 0.04481 seconds