How to assign a value to variable from select statement (PL/SQL Developer)

Question

I'm working with PL/SQL Developer.

I'm trying to update values in a column(existing table). The values used to populate rows should be auto incremented. The starting value is the maximum value that already exist in such field.

An example, I have the following table

ORDER_ID   T_NAME   T_PRICE
 20         CAR      50       
 NULL       VAN      100
 NULL       BIKE     10
 NULL       BOAT     300

After running the query I would expect the table to look like:

ORDER_ID   T_NAME   T_PRICE
 20         CAR      50       
 21         VAN      100
 22         BIKE     10
 23         BOAT     300

The query I created so far is:

DECLARE
  temp_order_id number;

BEGIN
  :temp_order_id = SELECT ISNULL(MAX((ORDER_ID)),0) + 1 FROM SALES_ACC;
update SALES_ACC
set (ORDER_ID)  = :temp_order_id , :temp_order_id = :temp_order_id + 1
where (ORDER_ID) is null;
END;

Oracle doesn't like assigning a value from select statement to the temp_order_id variable.

Does anyone has any idea how to fix it?

sequences work quite well see here http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314 — kevinskio, Jul 08 '15 at 15:18
sequences only work when adding new records to the table (am I right?), however I need a way to update existing records — Grentley, Jul 08 '15 at 15:22

score 4 · Accepted Answer · answered Jul 08 '15 at 15:20

You don't need pl/sql for this - you can do it in a single update statement - eg:

create table test1 as
select 20 order_id, 'CAR' t_name, 50 t_price from dual union all
select null order_id, 'VAN' t_name, 100 t_price from dual union all
select null order_id, 'BIKE' t_name, 10 t_price from dual union all
select null order_id, 'BOAT' t_name, 300 t_price from dual;


update test1
set order_id = (select max(order_id) from test1) + rownum
where order_id is null;

commit;

select * from test1
order by 1;

  ORDER_ID T_NAME    T_PRICE
---------- ------ ----------
        20 CAR            50
        21 VAN           100
        22 BIKE           10
        23 BOAT          300

drop table test1;

As a side note, it sounds like order_id is something that should really be the primary key of the table - if you had that, then you wouldn't be allowed to add a row without a value. Plus, you would also need a sequence that you would then use when inserting data into the table - e.g.:

insert into test1 (order_id, t_name, t_price)
values (test1_seq.nextval, 'TRIKE', 30);

score 2 · Answer 2 · edited May 23 '17 at 12:21

2

ORACLE's recomended way for this is either:

Create a sequence and a trigger on the table to assign order_id as soon as row is being inserted
or, for Oracle 12c, you can have an IDENTITY column

See How to create id with AUTO_INCREMENT on Oracle?, both approaches are described there.

edited May 23 '17 at 12:21

Community

1
1

answered Jul 08 '15 at 15:28

LiborStefek

400
4
16

Could you provide an example of how you would perform 1 or 2 in this context? – Patrick Bacon Jul 08 '15 at 17:14
Yes, but how do I update the table without sequence and trigger? – Grentley Jul 10 '15 at 16:21

score 2 · Answer 3 · answered Jul 08 '15 at 17:32

Within the DECLARE ... BEGIN ... END; section you are in PL/SQL syntax. That is not equal to the SQL syntax. Within PL/SQL syntax you should make use of the so called select into statement.

SELECT ISNULL(MAX((ORDER_ID)),0) + 1 
into :temp_order_id
FROM SALES_ACC

How to assign a value to variable from select statement (PL/SQL Developer)

3 Answers3