create table ranks (
    rank varchar(20)
);

create table people (
    name varchar(20)
);

insert into people values('Sam', 'Bob', 'Tim');

declare cursor c1 is (select substr(name, -1) from people)
begin
for i in c1
loop
    update ranks
    set rank = 'S'
    where i = 'S';
end loop;
end;

Hello, I am trying to use the last letter of the people table to decide who gets the S rank, but it isn't working. I keep getting - expression is of wrong type - error. Please help.

CodePudding user response：

Data model looks wrong. That should be only one table with two columns.

SQL> CREATE TABLE people
  2  (
  3     name   VARCHAR2 (20),
  4     RANK   VARCHAR2 (1)
  5  );

Table created.

SQL> INSERT INTO people (name) VALUES ('Sam');

1 row created.

SQL> INSERT INTO people (name) VALUES ('Bob');

1 row created.

SQL> INSERT INTO people (name) VALUES ('Tim');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM people;

NAME                 RANK
-------------------- -----
Sam
Bob
Tim

SQL>

Then, you don't need PL/SQL - a simple UPDATE will do. However, code you posted doesn't make much sense either - substr(name, -1) selects the last letter; nobody has a name that ends with an S so - no rows will ever be updated (at least, not for sample data). That's why I modified it to use the 1st letter.

SQL> UPDATE people
  2     SET RANK = 'S'
  3   WHERE SUBSTR (name, 1, 1) = 'S';

1 row updated.

SQL> SELECT * FROM people;

NAME                 R
-------------------- -
Sam                  S
Bob
Tim

SQL>

If it has to be PL/SQL (because you're learning it), then you'd

SQL> ROLLBACK;

Rollback complete.

SQL> BEGIN
  2     FOR cur_r IN (SELECT name FROM people)
  3     LOOP
  4        UPDATE people
  5           SET RANK = 'S'
  6         WHERE     name = cur_r.name
  7               AND SUBSTR (name, 1, 1) = 'S';
  8     END LOOP;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM people;

NAME                 RANK
-------------------- ----
Sam                  S
Bob
Tim

SQL>

CodePudding user response：

insert into people values('Sam', 'Bob', 'Tim');

Will fail as you only have one column and not three. You want to either use multiple inserts:

insert into people (name) values('Sam');
insert into people (name) values('Bob');
insert into people (name) values('Tim');

Or, use an INSERT ... SELECT ...

insert into people (name)
SELECT 'Sam' FROM DUAL UNION ALL
SELECT 'Bob' FROM DUAL UNION ALL
SELECT 'Tim' FROM DUAL;

Then you want something like:

begin
  for i in (select substr(name, -1) AS last_character from people)
  loop
    update ranks
    set rank = 'S'
    where i.last_character = 'S';
  end loop;
end;
/

But that can be simplified to get rid of the cursor and use a single UPDATE statement:

UPDATE ranks
SET    rank = 'S'
WHERE EXISTS(
  SELECT 1
  FROM   people
  WHERE  name LIKE '%S'
);

But neither of those will do anything as:

1. The ranks table contains zero rows.
2. None of the people have a name ending in S.

If you fix both of those then you will just end up updating every row in the ranks table as there is no relationship between a person and a rank.

db<>fiddle here