Home » SQL & PL/SQL » SQL & PL/SQL » Please explain UPDATE query (merged by LF)
Please explain UPDATE query (merged by LF) [message #255353] Tue, 31 July 2007 09:09 Go to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi,

Please explain me what is wrong in the following query

where in i m trying to update values in table XYZ with values from ABDC

UPDATE XYZ m SET
(name,age,sex,id) =
(SELECT x.age,x.sex)
FROM ABCD x
WHERE x.name=Y.name AND x.id=Y.id)WHERE Y.id>12 AND
(Y.name<>'EMY' OR Y.name<>'KATE')



thanks,

Gil.

[Updated on: Tue, 31 July 2007 11:53] by Moderator

Report message to a moderator

Re: please explain [message #255358 is a reply to message #255353] Tue, 31 July 2007 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please explain me what is wrong in the following query
error? what error? I don't see anything wrong.

Please read & FOLLOW posting guidelines in STICKY post at top of forum.
Re: please explain [message #255360 is a reply to message #255353] Tue, 31 July 2007 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not formatted and there is no Oracle version.
There is no error message.
There is no symptom something wrong.
There is no table description.
Do I have to go on?
Read the stickies.

Regards
Michel

Re: please explain [message #255369 is a reply to message #255353] Tue, 31 July 2007 10:16 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
your FORMATTED code is

UPDATE XYZ m SET
(name,age,sex,id) =(SELECT x.age,x.sex)
                    FROM ABCD x
                    WHERE x.name=Y.name AND x.id=Y.id)
WHERE Y.id>12 
AND (Y.name<>'EMY' OR Y.name<>'KATE')


is garbage. Where is the table with the Y alias?, Why do you have a close paren after x.sex? Why are you trying to set 4 columns and only returning 2 columns in your sub select?

The closest that I could figure out is the following, which I have NO way to know if it is correct.

UPDATE XYZ y SET
(age,sex) = (SELECT x.age,x.sex
                     FROM ABCD x
                     WHERE x.name=Y.name AND x.id=Y.id)
WHERE Y.id>12 
AND (Y.name<>'EMY' OR Y.name<>'KATE');
Re: please explain [message #255370 is a reply to message #255369] Tue, 31 July 2007 10:23 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi Bill,

thanks,
I made a spelling mistake in the alias while typing, but i used as given below, the error i got was missing right paranthesis, thats what i am confused of.

UPDATE XYZ Y SET
(age,sex) = (SELECT x.age,x.sex
FROM ABCD x
WHERE x.name=Y.name AND x.id=Y.id)
WHERE Y.id>12
AND (Y.name<>'EMY' OR Y.name<>'KATE');


thanks,

Gil




Re: please explain [message #255371 is a reply to message #255353] Tue, 31 July 2007 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I made a spelling mistake in the alias while typing
Don't you know how to CUT & PASTE between windows?

You're On Your Own (YOYO)!
Re: please explain [message #255374 is a reply to message #255370] Tue, 31 July 2007 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you formatted your query you can see where the error comes from.
Maybe if you show a little respect for those who can help you reading the sticky on top of the forum you can get useful answer.
As you seem to not be able to find them I post the URL:
How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
And I add:
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Also "please explain" is a very dummy title, how could you think it will help someone else searching for a solution? Or even you searching for the same one if everyone post "please explain" or "hi" or "query"...

Regards
Michel

[Updated on: Tue, 31 July 2007 10:41]

Report message to a moderator

update query [message #255382 is a reply to message #255353] Tue, 31 July 2007 11:13 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi ,

I apologise for posting the questing and code in the proper way.I tried follow the same way as it said in the link.

I m trying to update the table XYZ where the id>12 and the names is not 'KATHY' or 'EMY' with the values from ABC where the name and id have a match in each table.

UPDATE XYZ y 
       SET
   (dob,sex,age) 
          =
 (SELECT x.sex,
         x.age,
         x.dob
     FROM ABC x
  WHERE x.id=Y.id
    AND x.name=Y.name) 
WHERE Y.id>12 
  AND (Y.name<>'KATHY' 
  OR Y.ROW_MERGE_TYPE<>'EMY');


thanks,
Re: update query [message #255384 is a reply to message #255382] Tue, 31 July 2007 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your version?
What are the description of the tables?
Why you compare ROW_MERGE_TYPE with EMY and NAME with KATHY?
What is the problem?

Regards
Michel

Re: update query [message #255389 is a reply to message #255382] Tue, 31 July 2007 11:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
And the result is?

1. SET (dob,sex,age) = (SELECT x.sex, x.age, x.dob ...)
Do you have any reason for swapping columns?

2. the names is not 'KATHY' or 'EMY'
NOT (name = 'KATHY' OR name = 'EMY') is equivalent to (NAME <> 'KATHY' AND name <> 'EMY').
Just logic, see De Morgan's law.
Re: update query [message #255551 is a reply to message #255384] Wed, 01 August 2007 03:27 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
Hi Michel,

I apologise for keying in wrong text. oracle 9i (release 8.7.1.0.0) , the error message with the following query in right paranthesis missing. But i havent missed any.

  UPDATE ABC Y 
          SET
   (age, 
    sex,
    dob ) =
  (
     SELECT age,
          sex,
          dob   
     FROM XYZ x
    WHERE x.name=Y.name
	  x.id=Y.id 
                    ) 
 WHERE Y.id>12 
  AND  y.name<>'KATHY'


thanks,
Re: update query [message #255554 is a reply to message #255551] Wed, 01 August 2007 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your execution screen.

Regards
Michel
Re: update query [message #255569 is a reply to message #255554] Wed, 01 August 2007 04:10 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
SQL> desc abc;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
AGE NUMBER
SEX CHAR(1)
DOB DATE
ADDRESS VARCHAR2(30)
PHONE NUMBER
ID NUMBER

SQL> desc xyz;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
AGE NUMBER
SEX CHAR(1)
DOB DATE
ID NUMBER

SQL> select * from abc;

NAME AGE Sex DOB ADDRESS PHONE ID
---------- ---------- --- --------- -------------------- ---------- ----------

kathy abcdfsdf 2345 11


david efghi 5674 12


dj klmno 8765 10




SQL> select * from xyz;

NAME AGE S DOB ID
---------- ---------- - --------- ----------
kathy 26 f 12-DEC-80 11
david 25 m 04-MAY-81 12
dj 10 18-SEP-96 10


SQL>
UPDATE ABC Y SET 
             (age,sex,dob )
                   = 
       ( 
        SELECT age,sex, dob  
            FROM XYZ x 
       WHERE x.name=Y.name 
         AND x.id=Y.id
                       )
   WHERE Y.id>12 
     AND Y.name<>'KATHY';


x.id=Y.id) WHERE Y.id>12 AND Y.name<>'KATHY'
*
ERROR at line 2:
ORA-00907: missing right parenthesis


thanks,
Re: update query [message #255571 is a reply to message #255569] Wed, 01 August 2007 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a copy of your screen.

Regards
Michel
Re: update query [message #255580 is a reply to message #255571] Wed, 01 August 2007 04:22 Go to previous messageGo to next message
ursmadly
Messages: 13
Registered: July 2007
Location: united kingdom
Junior Member
hi michel,
i copied the from the sql screen to a notepad and then to the portal.

thanks
Re: update query [message #255587 is a reply to message #255580] Wed, 01 August 2007 04:29 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 01 August 2007 10:36
Use SQL*Plus and copy and paste your execution screen.

Regards
Michel


Previous Topic: continuous week number
Next Topic: Adding a new line between selected lines in sql query..
Goto Forum:
  


Current Time: Sun Dec 11 02:35:17 CST 2016

Total time taken to generate the page: 0.08415 seconds