How to increase the value of a column in a table by a constant by consulting another table

join;oracleupdate

I have prepared the database of the college given as

create table depts(
deptcode char(3) primary key,
deptname char(70) not null);

create table students(
rollno number(2) primary key,
name char(50),
bdate date check(bdate < TO_DATE('2004-01-01','YYYY-MM-DD')),
deptcode char(3) references depts(deptcode)
on delete cascade,
hostel number check(hostel<20),
parent_inc number(8,1));

create table faculty(
fac_code char(2) primary key,
fac_name char(50) not null,
fac_dept char(3) references depts(deptcode)
on delete cascade);


create table crs_offrd(
crs_code char(5) primary key,
crs_name char(35) not null,
crs_credits number(2,1),
crs_fac_cd char(2) references faculty(fac_code)
on delete cascade);

create table crs_regd(
crs_rollno number(2) references students(rollno) on delete cascade,
crs_cd char(5) references crs_offrd(crs_code)
on delete cascade,
marks number(5,2),
primary key(crs_rollno,crs_cd));

Now I want to give Grace Marks 5 in subject ‘DBMS’to the students who have scored less than 50 in that subject.

Accordingly I wrote the query as:

update r
set r.marks=r.marks+5
from crs_regd r
inner join crs_offrd o
on r.crs_cd=o.crs_code
where o.crs_name='DBMS' and r.marks<50;

but it shows error as SQL command not properly ended in line 3. I am using ORACLE SQL. What is the problem in this query?

Best Answer

The UPDATE syntax differs a lot from DBMS to DBMS, especially when there are joins. For Oracle this would work:

update crs_regd r
set marks = marks + 5
where marks < 50
  and exists
      ( select 1
        from crs_offrd o
        where r. crs_cd = o. crs_code
          and o. crs_name = 'DBMS'
      ) ;