Home » SQL & PL/SQL » SQL & PL/SQL » Update with Sequence using Order By (Oracle 9.0.1, VMS)
Update with Sequence using Order By [message #402602] Mon, 11 May 2009 11:01 Go to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Can someone help me with this. I've tried everything short of using a cursor and a loop. I'm trying to, as much as possible, stick to SQL, and stay away from the PL. The other folks here are uneasy and unfamiliar with stored procedures, so I'm trying to go slow. But if I've got to use PL and a cursor, I guess I will, but I'm just having a tough time understanding why Oracle can't do this without a cursor.

I have a SEQUENCE, and I'm using it to maintain the order that steel pieces will be fed into a furnace. I can use the rownum to "pack" the returned results, so I don't care if the sequence number is interrupted, but I need it to be in the correct order, and Oracle seems to insist on taking unordered rows to optimize the update statement.

I'm hoping to do this:

UPDATE lnupnum FROM slblnup
WHERE lnupnum >= s_InsAboveLnupNum
AND lnupnum < s_InsBelowLnupNum
ORDER BY lnupnum;

Of course, it doesn't like the ORDER BY clause. I've tried using where-in and doing the select-order by, but it still rejects it. I've tried various other ways to do the ordering in a subquery, but all to no avail.

Any suggestions would be welcome.
Re: Update with Sequence using Order By [message #402605 is a reply to message #402602] Mon, 11 May 2009 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
UPDATE lnupnum FROM slblnup
WHERE lnupnum >= s_InsAboveLnupNum
AND lnupnum < s_InsBelowLnupNum
ORDER BY lnupnum;

above is not valid syntax

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Update with Sequence using Order By [message #402611 is a reply to message #402602] Mon, 11 May 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sal from emp order by empno;
       SAL
----------
       800
      1600
      1250
      2975
      1250
      2850
      2450
      3000
      5000
      1500
      1100
       950
      3000
      1300

14 rows selected.

SQL> update emp a
  2  set sal = sal + 
  3            (select rn 
  4             from (select rownum rn, rid
  5                   from (select rowid rid
  6                         from emp
  7                         order by empno)
  8                  ) b
  9             where b.rid = a.rowid)
 10  /

14 rows updated.

SQL> select sal from emp order by empno;
       SAL
----------
       801
      1602
      1253
      2979
      1255
      2856
      2457
      3008
      5009
      1510
      1111
       962
      3013
      1314

14 rows selected.

This is one way many others are available.

Regards
Michel
Re: Update with Sequence using Order By [message #402612 is a reply to message #402611] Mon, 11 May 2009 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Any here's one less version 7:
SQL> rollback;

Rollback complete.

SQL> merge into emp a
  2  using (select empno, row_number() over(order by empno) rn from emp) b
  3  on (b.empno = a.empno)
  4  when matched then update set sal = sal + b.rn
  5  /

14 rows merged.

SQL> select sal from emp order by empno;
       SAL
----------
       801
      1602
      1253
      2979
      1255
      2856
      2457
      3008
      5009
      1510
      1111
       962
      3013
      1314

14 rows selected.

Regards
Michel
Re: Update with Sequence using Order By [message #402613 is a reply to message #402605] Mon, 11 May 2009 11:22 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@rboggess,

Can you please post an example explaining your requirement. Sample data with your desired result help people understand your requirement better.

Also go through Update Statement.

Regards,
Jo

[**Added**]
I think Michel figured it out. Didn't see his posts.

[Updated on: Mon, 11 May 2009 11:24]

Report message to a moderator

Re: Update with Sequence using Order By [message #402619 is a reply to message #402605] Mon, 11 May 2009 12:17 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
And can you recommend a changes that would accomplish the equivalent and by syntactically correct?

I can update a single value, I can update the entire contents of the table, but I can't update the entire contents of the table in sequential order. Is there any way, short of a PL/SQL Loop with a cursor, to update a subset of the table with new numbers that preserve the relative order of the result set?
Re: Update with Sequence using Order By [message #402620 is a reply to message #402611] Mon, 11 May 2009 12:22 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Thank you! I think I can make this work. I'm trying to keep the sequence updated, but if it will accept the sub-query in this format, I should be able to modify it to use the sequence.
Re: Update with Sequence using Order By [message #402628 is a reply to message #402619] Mon, 11 May 2009 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want an answer with your own table you have to post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Update with Sequence using Order By [message #402637 is a reply to message #402628] Mon, 11 May 2009 13:13 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
  CREATE TABLE SLBLNUP
  (
    COILID VARCHAR2(7 BYTE) NOT NULL,
    FCENUM CHAR(1 BYTE),
    LNUPNUM NUMBER(38, 0),
    LOCATION CHAR(1 BYTE),
    TIMSTP DATE
    , CONSTRAINT SLBLNUP_COILID3 PRIMARY KEY
    (
      COILID
    )
    ENABLE
  )
/
SHOW ERRORS

  ALTER TABLE SLBLNUP
  ADD CONSTRAINT SLBLNUP_COILD2 FOREIGN KEY
  (
    COILID
  )
  ;
/
SHOW ERRORS

  CREATE SEQUENCE SLBLNUPSEQ INCREMENT BY 1 MINVALUE 1 CACHE 20 ORDER ;
/
SHOW ERRORS

  CREATE TABLE TMPSLB
  (
    COILID VARCHAR2(7 BYTE) NOT NULL,
    FCENUM CHAR(1 BYTE),
    LNUPNUM NUMBER(38, 0),
    LOCATION CHAR(1 BYTE),
    TIMSTP DATE
  )
  ;
/
SHOW ERRORS

  Insert into slblnup (COILID, FCENUM, LNUPNUM, LOCATION, TIMSTP) 
  VALUES ('D36801', '4', SLBLNUPSEQ.NEXTVAL, 'L', CURRENT_DATE);

  Insert into slblnup (COILID, FCENUM, LNUPNUM, LOCATION, TIMSTP) 
  VALUES ('D36802', '4', SLBLNUPSEQ.NEXTVAL, 'L', CURRENT_DATE);

  Insert into slblnup (COILID, FCENUM, LNUPNUM, LOCATION, TIMSTP) 
  VALUES ('D36803', '4', SLBLNUPSEQ.NEXTVAL, 'L', CURRENT_DATE);

  Insert into slblnup (COILID, FCENUM, LNUPNUM, LOCATION, TIMSTP) 
  VALUES ('D36804', '4', SLBLNUPSEQ.NEXTVAL, 'L', CURRENT_DATE);

  INSERT INTO TMPSLB (COILID, LNUPNUM)
  VALUES ('D36805', 1)

  INSERT INTO TMPSLB (COILID, LNUPNUM)
  VALUES ('D36806', 2)

-- I need to insert D36805 and D36806 above D36803.
-- To accomplish this, my idea was to store the lnupnum
-- from D36803 in one variable, store the sequence's nextval
-- in another, insert the rows I need, and then update the 
-- lnupnum values between the two lnupnums stored in the 
-- two variables.

-- My stored procedure looks something like this.
  CREATE OR REPLACE
  PROCEDURE MoveCoilsToLineup
  (
    s_InsAboveCoil IN VARCHAR2 )
  AS
  BEGIN
    DECLARE
      s_InsAboveLnupnum NUMBER;
      s_InsBelowLnupnum NUMBER;
    BEGIN
      SELECT slblnupseq.nextval INTO s_InsBelowLnupnum FROM DUAL;
      SELECT lnupnum
      INTO s_InsAboveLnupnum
      FROM slblnup
      WHERE coilid LIKE s_InsAboveCoil;

      INSERT
      INTO slblnup
      (
        coilid,
        fcenum,
        lnupnum,
        location,
        timstp
      )
      (SELECT CoilId,
          '4',
          SLBLNUPSEQ.NEXTVAL,
          'L',
          CURRENT_DATE
        FROM
          (SELECT tmpslb.coilid,
           FROM tmpslb
          ORDER BY CoilId ASC
          )
      );

    DELETE FROM tmpslb;

UPDATE SLBLNUP
SET lnupnum =
slblnupseq.nextval
WHERE lnupnum >= s_InsAboveLnupnum
AND lnupnum    < s_InsBelowLnupnum
AND location LIKE 'L';

     COMMIT;
    dbms_output.put_line ( 'Changes saved.' ) ;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    dbms_output.put_line ( 'Failed to save changes.' ) ;
  END;
END MoveCoilsToLineup;

DROP TABLE SLBLNUP;
DROP TABLE TMPSLB;
DROP SEQUENCE SLBLNUPSEQ;
DROP PROCEDURE MoveCoilsToLineup;


Sorry, I was working on it. And I also wanted to try to figure it out for myself. Embarassed

But I still can't get it to work. I haven't actually run the above, so it might have a couple of (I hope) obvious errors. This isn't exactly the steup I have, but it should be close enough. I also have a production data table that I'm using to select "missing" data from when I populate the slblnup table. I left it out, since the problem appears to be only in the slblnup.lnupnum numbering.

Now, mind you, this works sometimes. I believe to make this "mess up", you have to delete one or two records (presumably to open up a space), then insert somewhere that would make oracle want to use the empty space. I'm sure you know that without specifying an order by, Oracle will optimize the record order and apply the numbering accordingly.

What I'm looking for, is to force Oracle to use the Sequence in such a way as to maintain the final ordering of the CoilIds as
D36801, D36802, D36805, D36806, D36803, and D36804. If lnupnum ends up 678, 731, 732, 748, 750 etc., that's fine -- just so long as the order is preserved.

I'm sure the answer is in the code you've already shown me. Somehow I need to tell Oracle that it should maintain the row order of an internal order by lnupnum, but I'm just not getting it to compile with the order by in there.
Re: Update with Sequence using Order By [message #402641 is a reply to message #402637] Mon, 11 May 2009 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Somehow I need to tell Oracle that it should maintain the row order of an internal order by lnupnum

There is no row order.
You have to choose a field to indicate the order; which one is it?

Regards
Michel
Re: Update with Sequence using Order By [message #402647 is a reply to message #402641] Mon, 11 May 2009 15:25 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Post Edit: I should also point out that it's the last part of the stored procedure that I can't get working because I don't know how to ORDER BY lnupnum.
UPDATE SLBLNUP
SET lnupnum =
slblnupseq.nextval
WHERE lnupnum >= s_InsAboveLnupnum
AND lnupnum    < s_InsBelowLnupnum
AND location LIKE 'L';


Sorry, for not being more clear. The lnupnum of slblnup table is the order. The idea was that the data in slblnup was already in some order (as is stored in sort-order by the lnupnum field), and the insert should not change that order except for the insertion. So, I wouldn't need to reevaluate the lnupnum using the slblnupseq sequence for those data below the insertion point. The new pieces would get inserted with new (higher numbers) in the slblnupseq sequence, and then those between these newly inserted pieces, and those above the insertion point's lnupnum would continue to get renumbered with the slblnupseq sequence.

I know that ORDER BY is not syntactically correct, but I sure do whish it were. If I create a cursor with the update converted into a select, add the ORDER BY lnupnum, I can create a For-record-in-cursor-loop, and zip right through it. That's what I've done, actually, but the other folks here are getting nervous as I implement all these new-fangled stored procedures. So I compromised by offering to limit them as much as reasonable to straight SQL with parameters. (They're used to doing ad-hoc queries in the client code, but I'm hoping to move away from that to help isolate potential changes during maintenance.)

Looking at what you had working on the rownum and rowid example, I'm thinking I need to embed an order by lnupnum and retrieve the rowid, then use the rowid in the outer update statement where they match. I just couldn't get it to compile. (I couldn't use the merge because the final deployment is earlier than 9.)

[Updated on: Mon, 11 May 2009 15:32]

Report message to a moderator

Re: Update with Sequence using Order By [message #402680 is a reply to message #402647] Tue, 12 May 2009 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I don't understand you.
You have 2 tables in your test case:
SQL> select * from slblnup;
COILID  F    LNUPNUM L TIMSTP
------- - ---------- - -------------------
D36801  4          1 L 12/05/2009 07:51:27
D36802  4          2 L 12/05/2009 07:51:27
D36803  4          3 L 12/05/2009 07:51:27
D36804  4          4 L 12/05/2009 07:51:27

4 rows selected.

SQL> select * from  TMPSLB ;
COILID  F    LNUPNUM L TIMSTP
------- - ---------- - -------------------
D36805             1
D36806             2

2 rows selected.

What is the relation between the 2?
What is the result your want?

Regards
Michel
Re: Update with Sequence using Order By [message #402725 is a reply to message #402647] Tue, 12 May 2009 03:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You should be able to use an update view to impose an order:
create table test_196(col_1  number, col_2 number);

create sequence seq_196;

insert into test_196 (col_1) select level from dual connect by level <= 10;

commit;

update (select col_2 
        from   test_196
        order by col_1 desc)
set col_2=seq_196.nextval;

select * from test_196;
Re: Update with Sequence using Order By [message #402785 is a reply to message #402680] Tue, 12 May 2009 08:18 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Quote:
What is the relation between the 2?
What is the result your want?



Ok, here's the way it's supposed to work: I have a program (HMI) that interacts with the operator and this database. The operator chooses a set of pieces from the proddata table (not included). The only thing I get from the proddata table is the Coil Id. When the operator's selection is dropped on another control, I fill the tmpslb.coilid with the Coil ID, and tmpslb.lnupnum with a number, from 1 to n, the number of pieces dragged and dropped, in the order they were selected from 1 to n.

In the program, I determine which row the operator dropped the pieces onto, I find the Coil ID of that row, and I pass that to this stored procedure. This stored procedure should use the sequence slblnupseq, the tmpslb table, and the CoilId I pass in to renumber the necessary pieces to create the new slblnup.lnupnum order.

To accomplish that, I start by looking up the slblnup.lnupnum of the CoilId I passed in, and I store that in a variable s_InsAboveLnupnum. Then I would like to store the current value of the sequence, but I can't, and a skipped number is no big deal, so I grab the nextval from the sequence, and I store that in the variable s_InsBelowLnupnum.

Then, I do a join with proddata and tmpslb (to get the furnace number, fcenum), and I insert these into the slblnup table, ordered by tmpslb.lnupnum, using tmpslb.coilid for slblnup.coilid, proddata.fcenum for slblnup.fcenum, slblnupseq.nextval for slblnup.lnupnum, current_date for slblnup.timstp, and 'L' for the slblnup.location.

These pieces all have a lnupnum greater than my variable s_InsBelowLnupnum. But I still have to renumber the pieces already in the slblnup table from the drop coilid to the end. This is what I can't get to work without a cursor. I need to update the slblnup.lnupnum with slblnupseq.nextval where slblnup.lnupnum is between s_InsAboveLnupnum (the lineup number of the coild where the selection was dropped) and s_InsBelowLnupnum (the sequence number that starts where the new pieces were added to the lineup, that is, the previous end of the lineup before the insertion of the new pieces). This would maintain the proper slblnup.lnupnum order of the pieces.

For now, I've declared
CURSOR c1
IS
SELECT lnupnum 
FROM slblnup
WHERE lnupnum < s_InsBelowLnupnum
AND lnupnum >= s_InsAboveLnupnum
ORDER BY lnupnum;


Then I use the FOR-LOOP to update the lnupnum:
FOR lnuprec IN c1
LOOP
UPDATE slblnup
SET lnupnum = slblnupseq.nextval
WHERE lnupnum = lnuprec.lnupnum;


But I'd like to be able to do that in SQL, rather than in a cursor for-loop. Is that possible? (Apologies, JRowbottom, I haven't looked at your example yet as I post this, so I don't know if that's the answer or not.)
Re: Update with Sequence using Order By [message #402789 is a reply to message #402725] Tue, 12 May 2009 08:35 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Quote:
UPDATE
(SELECT slblnup.lnupnum
FROM slblnup, tmpslb
WHERE slblnup.coilid = tmpslb.coilid
ORDER BY tmpslb.lnupnum)
SET lnupnum = slblnupseq.nextval



Mad ORA-01779: cannot modify a column which maps to a non key-preserved table

If it had a leg, I'd bite it!

Rats! This was exactly what I was looking for. What does this mean, "key-preserved table"? Sorry, I'll go RTFM and get back to you. This might be something I'm allowed to change in the table definition.
Re: Update with Sequence using Order By [message #402790 is a reply to message #402785] Tue, 12 May 2009 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't read your post. I simply want you to answer the question:
With the data you posted, what is the result your want?
Post it as I posted the content of the tables.
What should be this content after processing?

I don't want any algorithm you thougt about, just requirements. There is a starting data, process requirements specify what will be the target.

Regards
Michel


Re: Update with Sequence using Order By [message #402791 is a reply to message #402790] Tue, 12 May 2009 08:48 Go to previous messageGo to next message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Oh, ok. (You did ask for the relation between the two tables, though.)

Assuming I passed 'D36803' into the stored procedure, I need to get the results as follows:

SELECT * FROM slblnup ORDER BY lnupnum;
COILID  F    LNUPNUM L TIMSTP
------- - ---------- - -------------------
D36801  4          1 L 12/05/2009 07:51:27
D36802  4          2 L 12/05/2009 07:51:27
D36805  4          7 L 12/05/2009 09:45:30
D36806  4          8 L 12/05/2009 09:45:30
D36803  4          9 L 12/05/2009 07:51:27
D36804  4         10 L 12/05/2009 07:51:27


BTW, thanks for taking the time to look at this.
Re: Update with Sequence using Order By [message #402794 is a reply to message #402789] Tue, 12 May 2009 08:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmm - I'd missed that there were two tables involved.

Does this work:
UPDATE
(SELECT slblnup.lnupnum
FROM slblnup
WHERE slblnup.coilid IN (SELECT tmpslb.coilid
                         FROM   tmpslb)
ORDER BY slblnup.lnupnum)
SET lnupnum = slblnupseq.nextval

Re: Update with Sequence using Order By [message #402797 is a reply to message #402794] Tue, 12 May 2009 09:02 Go to previous message
rboggess
Messages: 15
Registered: May 2009
Location: Pittsburgh
Junior Member
Quote:
Hmm - I'd missed that there were two tables involved.



Je suis un idiot! My mistake! Let me give it another go. I'm getting addled trying to put out too many fires. I already had that portion working, it was the part without two tables that was the problem.
Previous Topic: dbms_utility.comma_to_table (merged)
Next Topic: ANY keywork doesn't work with LIKE?? (merged)
Goto Forum:
  


Current Time: Sat Dec 10 06:58:19 CST 2016

Total time taken to generate the page: 0.08868 seconds