count function inside case statement

Question

Problem statement:

If the number of courses an instructor teaches is less than 1 then update the salary to 30000 else to 35000 - using one command. The code I wrote results in an error, could you please tell why it's not working and how I can improve it. Thanks in advance :D

UPDATE using oracle sql

schema:

instructor -> id, name, dept_name, salary

teaches -> id, course_id, semester, year

update i
set i.salary = case
when count(t.course_id) < 1 then 30000
else 35000
from (select * from instructor i inner join teaches t on i.id = t.id)

What's the schema of instructor and teaches? Also which sql database are you using? Is it mysql? — SMA
– SMA, Commented Feb 17, 2018 at 18:24
instructor -> id, name, dept_name, salary teaches -> id, course_id, semester, year I am using oracle sql — alice_schizoid
– alice_schizoid, Commented Feb 17, 2018 at 18:28

Littlefoot · Accepted Answer · 2018-02-17 18:32:09Z

Here's an example; I created my own tables (as you didn't provided yours), I hope it'll make sense.

SQL> create table instructor (id_instructor number, salary number);

Table created.

SQL> insert into instructor values (1, 100);

1 row created.

SQL> insert into instructor values (2, 100);

1 row created.

SQL>
SQL> create table teaches (id_instructor number, id_course number);

Table created.

SQL> insert into teaches values (1, 1);

1 row created.

SQL> insert into teaches values (1, 2);

1 row created.

SQL>

As teacher ID = 2 teaches "nothing", his salary should be 30000. On the other hand, teacher ID = 2 teaches 2 classes so he'll get 35000.

SQL> update instructor i set
  2    i.salary = (select case when count(*) < 1 then 30000
  3                            else 35000
  4                       end
  5                from teaches t
  6                where t.id_instructor = i.id_instructor);

2 rows updated.

SQL> select * from instructor;

ID_INSTRUCTOR     SALARY
------------- ----------
            1      35000
            2      30000

SQL>

Gordon Linoff · Accepted Answer · 2018-02-17 19:09:25Z

1

I wouldn't recommend count(*) in the subquery. count(*) < 1 is really saying that no row exists. You are using an aggregation when exists is appropriate -- and that is a performance hit.

So a better approach is:

update instructor i
     set salary = (select case when exists (select 1 from teaches t where t.id_instructor = i.id_instructor)
                          then 30000 else 35000
                   end);

If you are learning SQL, you should be learning the best way to do things.

answered Feb 17, 2018 at 19:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Prabhath Amaradasa · Accepted Answer · 2018-02-17 18:34:45Z

0

update i
set i.salary = (case when t.id  IS NULL  then 30000 else 35000 END)
from  instructor i
LEFT OUTER  join teaches t on i.id = t.id

answered Feb 17, 2018 at 18:34

Prabhath Amaradasa

5032 silver badges7 bronze badges

1 Comment

Rosário P. Fernandes Over a year ago

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Collectives™ on Stack Overflow

count function inside case statement

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related