CODEWITHSUNDEEP
oracle-databaseplsql
Marko
Marko

Reputation: 1617

LOOP update with column size

I tried to create PL/SQL script that would update my table with column size. My table looks like this:

| ID | TEXT | SIZE |
--------------------
| 1  | .... | null |
| 2  | .... | null |
| 3  | .... | null |
...

I want the PL/SQL script to fill the size column depending of the length of text for a certain document and then delete the contents of the TEXT column.

Here's what I've tried:

DECLARE 
cursor s1 is select id from table where size is null; 
BEGIN  for d1 in s1 loop
    update table set size = (select length(TEXT) from table where id = d1) where id=d1;  
end loop; 
END; 
/

Upvotes: 1

Views: 445

Answers (1)

Peter Lang
Peter Lang

Reputation: 55524

Unless there is a good reason, do this in pure SQL (or put the following statement into PL/SQL):

UPDATE t
SET size = LENGTH(text),
    text = NULL
WHERE size IS NULL;

This is both easier to read and faster.

Upvotes: 4

Related Questions