My first for loop in SQL

Question

/*alter table schematics
add type_id NUMBER;*/

begin

for i in  (
select a.id,
       substr(a.key,
              instr(a.key, '|') + 1,
              (instr(a.key, '|', 1, 2) -
              (instr(a.key, '|', 1, 1) + 1))) TYPE_ID
  from schematics a)
  loop 
    update schematics
    set type_id = TYPE_ID;
  end loop;
)
end

Hey guys, can you help me out? TYPE_ID gives me a bunch of IDs and I want to store them in my newly created column using a for loop..first time using a for in sql, no idea how to do it. Any help? Thanks in advance.

Answer 1

You are trying to update the newly created column with a value calculated from another column. This is usually done with a mere update:

update schematics
set type_id = 
  substr(key,
         instr(key, '|') + 1, 
         instr(key, '|', 1, 2) - instr(key, '|', 1, 1) - 1);

If you want to do the same in PL/SQL with a loop for every single record, you'd select the key and the calculated value to use both for the update. But that seems like overkill.

begin
  for rec in
  (
    select distinct 
      key,
      substr(key,
             instr(key, '|') + 1, 
             instr(key, '|', 1, 2) - instr(key, '|', 1, 1) - 1) as type_id
    from schematics
  ) loop 
    update schematics
    set type_id = rec.type_id
    where key = rec.key;
  end loop;
end;

Answer 2

You do not need a for loop. Furthermore there are no loops in pure SQL... Just because you generally don't need them. There are loops in procedural extensions such as PL/SQL (Oracle) or T/SQL (SQL server) but they really do not solve your problem.

Just use a simple update

update schematics set type_id = substr(akey,
              instr(key, '|') + 1,
              (instr(key, '|', 1, 2) -
              (instr(key, '|', 1, 1) + 1)))

(Maybe a where clause could be required later)