Subquery and update statement : IN « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. IN
SQL>
SQL>
SQL> create table course_schedule
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , trainer    NUMBER(4)
  5  , location   VARCHAR2(20)) ;

Table created.

SQL>
SQL>
SQL> insert into course_schedule values ('SQL',date '1999-04-12',1,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('OAU',date '1999-08-10',2,'CHICAGO');

1 row created.

SQL> insert into course_schedule values ('SQL',date '1999-10-04',3,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('SQL',date '1999-12-13',4,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('JAV',date '1999-12-13',5,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('XML',date '2000-02-03',6,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('JAV',date '2000-02-01',7,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('PLS',date '2000-09-11',8,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('XML',date '2000-09-18',NULL,'SEATTLE');

1 row created.

SQL> insert into course_schedule values ('OAU',date '2000-09-27',9,'DALLAS' );

1 row created.

SQL> insert into course_schedule values ('ERM',date '2001-01-15',10, NULL    );

1 row created.

SQL> insert into course_schedule values ('PRO',date '2001-02-19',NULL,'VANCOUVER' );

1 row created.

SQL> insert into course_schedule values ('RSD',date '2001-02-24',8,'CHICAGO');

1 row created.

SQL>
SQL> create table registrations
  2  ( attendee    NUMBER(4)
  3  , course      VARCHAR2(6)
  4  , begindate   DATE
  5  , evaluation  NUMBER(1)) ;

Table created.

SQL>
SQL>
SQL> insert into registrations values (1, 'SQL',date '1999-04-12',4   );

1 row created.

SQL> insert into registrations values (2, 'SQL',date '1999-12-13',NULL);

1 row created.

SQL> insert into registrations values (3, 'SQL',date '1999-12-13',NULL);

1 row created.

SQL> insert into registrations values (4, 'OAU',date '1999-08-10',4   );

1 row created.

SQL> insert into registrations values (5, 'OAU',date '2000-09-27',5   );

1 row created.

SQL> insert into registrations values (6, 'JAV',date '1999-12-13',2   );

1 row created.

SQL> insert into registrations values (7, 'JAV',date '2000-02-01',4   );

1 row created.

SQL> insert into registrations values (8, 'JAV',date '2000-02-01',5   );

1 row created.

SQL> insert into registrations values (9, 'XML',date '2000-02-03',4   );

1 row created.

SQL> insert into registrations values (10,'XML',date '2000-02-03',5   );

1 row created.

SQL> insert into registrations values (1, 'PLS',date '2000-09-11',NULL);

1 row created.

SQL> insert into registrations values (2, 'PLS',date '2000-09-11',NULL);

1 row created.

SQL> insert into registrations values (3, 'PLS',date '2000-09-11',NULL);

1 row created.

SQL>
SQL>
SQL> update registrations
  2  set    evaluation = 1
  3  where (course,begindate)
  4     in (select course,begindate
  5         from   course_schedule
  6         where  location = 'CHICAGO');

1 row updated.

SQL>
SQL> drop table course_schedule;

Table dropped.

SQL>
SQL> drop table registrations;

Table dropped.

SQL>








2.17.IN
2.17.1.Using the IN Operator
2.17.2.Use null value with IN operator
2.17.3.Use varchar2 type value with IN operator
2.17.4.Use in with subquery
2.17.5.Map pair with subquery by using IN operator
2.17.6.Get all managers
2.17.7.List all non-manager employee
2.17.8.Combine subquery and other conditions
2.17.9.Subquery and update statement
2.17.10.In subquery
2.17.11.In and subquery
2.17.12.Value pair and in operator