Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: I am so confused.........

    Please tell me what is wrong with this query:

    update sumjug s
    set s.ad_flag = (select j.'A' from tjuggernaut j
    where s.list_phone_no = j.list_phone_no
    and j.ad_flag = 'Y'
    where exists (select null from 'A' j
    where s.list_phone_no = j.list_phone_no
    and j.ad_flag = 'Y';

    I am getting ORA-01747: invalid user.table.column, table.column, or column specification errors

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Every programming language I know about requires matching pairs of open and closing parenthesis.

    Please explain why you thought that two "(" without any ")" is proper SQL coding.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    aside from the syntax errors mentioned, what is:
    select j.'A' from tjuggernaut j

    why does A have single quotes around it? Is it supposed to be a column in tjuggernaut?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I suggest writing procedures with cursors instead of
    all these crappy update statements.

    for you it would be much more clear what you are trying to update.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2004
    Posts
    35

    Exclamation bunch of syntax errors

    there are many syntax errors , i think you have pasted a wrong query
    here
    eg.
    no proper braces,alias duplicates 'j',where clauses.

    correct them.

    skg

  6. #6
    Join Date
    Apr 2004
    Posts
    113
    Thanks for everyone's response.
    As I have said before, I am filling in for someone and I am in no way a programmer.
    You are correct I did for get to close my parentheses.
    update sumjug s
    set s.ad_flag = (select j.'A' from tjuggernaut j
    where s.list_phone_no = j.list_phone_no
    and j.ad_flag = 'Y')
    where exists (select null from 'A' j
    where s.list_phone_no = j.list_phone_no
    and j.ad_flag = 'Y');


    The quotes around the A and the Y signify a value. The values for the column ad_flag are either A or Y.

    The Duck, I really do not know how to write a procedure with cursors. If you would like to help me with that I can surely try.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by The_Duck
    I suggest writing procedures with cursors instead of
    all these crappy update statements.

    for you it would be much more clear what you are trying to update.
    I take it you mean that these particular update statements are "crappy" and not update statements in general? A single update statement is generally to be preferred over a cursor-based approach, but you are suggesting the cursor-based approach is easier for a novice, right?

  8. #8
    Join Date
    Apr 2004
    Posts
    113
    Here is the original sql statement that I tried to convert:
    We are converting Sybase to Oracle.

    update sumjug
    set ad_flag = 'A'
    from sumjug s, tjuggernaut j
    where s.list_phone_no = j.list_phone_no
    and j.ad_flag = 'Y';

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try

    update sumjug s
    set s.ad_flag = 'A'
    where exists (select 'X'
    from tjuggernaut j
    where s.list_phone_no = j.list_phone_no
    and j.ad_flag = 'Y');


    By the way, if the tables are big, you might think about putting an index on tjuggernaut (list_phone_no). The exist select whill need to be run for every row in sumjug. You would create the index by typing

    create index tjuggernaut_i1 on tjuggernaut(list_phone_no);

    is sumjog summary information of tjuggernaut? If it is, the table might be able to be replaced with a view so that it would dynamitically be maintained.
    Last edited by beilstwh; 08-20-04 at 12:29.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •