Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    12

    Question Unanswered: One small problem with SQL syntax...

    Hello. I have following query. It does not work and complains ORA-00993 SQL not properly ended. I know select is right for it works. It gives me multiple lines. Now, I need to update rows in a table DELIVERYBATCH, which are those which SELECT gives. However, there is something wrong in my INSERT. I would be grateful to know what it might be. Any help appreciated.

    insert into DELIVERYBATCH(deliverydate) values ('19991231000000') where unitname in (SELECT a.UNITNAME FROM
    UNITVISIT a,DELIVERYBATCH b WHERE a.STATUS2 = 'ARRIVED' AND
    a.STARTDATE > '20011231000000' AND a.ENDDATE IS NULL AND
    a.STATUS = 'A' AND a.FILLEDSTATUS = 'F' AND
    a.VISITID = b.VISITID AND
    a.terminal = 'HMN' );
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

  2. #2
    Join Date
    May 2003
    Posts
    12
    Ah, sorry, I must be on sleep or something. I need to use UPDATE, not insert... Stupid me. Forget this
    ------------------------------------
    Long days and pleasant nights
    - Roland the gunslinger
    ------------------------------------

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: One small problem with SQL syntax...

    Originally posted by Karibu
    Hello. I have following query. It does not work and complains ORA-00993 SQL not properly ended. I know select is right for it works. It gives me multiple lines. Now, I need to update rows in a table DELIVERYBATCH, which are those which SELECT gives. However, there is something wrong in my INSERT. I would be grateful to know what it might be. Any help appreciated.

    insert into DELIVERYBATCH(deliverydate) values ('19991231000000') where unitname in (SELECT a.UNITNAME FROM
    UNITVISIT a,DELIVERYBATCH b WHERE a.STATUS2 = 'ARRIVED' AND
    a.STARTDATE > '20011231000000' AND a.ENDDATE IS NULL AND
    a.STATUS = 'A' AND a.FILLEDSTATUS = 'F' AND
    a.VISITID = b.VISITID AND
    a.terminal = 'HMN' );
    I don't understand what you want to do here. You say you want to update rows in the table, so why the INSERT statement?

    This part on its own is a valid INSERT:

    insert into DELIVERYBATCH(deliverydate) values ('19991231000000');

    But the next part doesn't make sense in the context of the insert:

    where unitname in ...

    What "unitname" does it refer to? The unitname of some already existing record in the table?

    If you mean to UPDATE existing rows where unitname is in the subquery list, use an UPDATE statment.

Posting Permissions

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