问题描述:

i have below two tables:

EMPLOYEE (employee_ID primary key)(Foreign key Department_id reference department(department_id))

employee_id First_name Last_name Department_id Salary Commission

154 Aaan Eaan 80 6000 0.2

166 Baan Faan 80 7000 0.1

167 Caan Gaan 80 7000 0.3

169 Daan Haan 80 8000 0.4

DEPARTMENTS(Department_id primary key)

Department_id Dept_name location

10 AA 1700

40 BB 1800

70 CC 1900

80 DD 2000

I have below update code

UPDATE employee a

SET department_id=(select department_id

from departments

where location = 2100),

(salary,comission)=(select 1.1*AVG(salary), 1.5*AVG(comission)

from employee b

where a.department_id = b.department_id)

where first_name||' '||last_name = 'Caan Gaan';

My questions is will above code execute? There is no location 2100 in departments table, will null value inserted?Please help

网友答案:

Yes NULL will be inserted.

You are setting the department_id using a scalar subquery. Such a subquery returns exactly one column and at most one row. If there is no row in the result set, then the value is NULL.

If a location could have more than one departments, then your query is dangerous. If it returns multiple values, then you will get an error. One solution is to add where rownum = 1. Another is to use an aggregation function to ensure that only one value is returned.

网友答案:

It'll either be returning:

1) null

OR

2) Error if the department_id is set to NOT NULL or it is a Primary key

网友答案:

The above code will be executed.Yes, null value is inserted in this case. Please read the oracle docs here - http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#sthref3085

It will give you the main concept why null value will insert in Foreign Key column.

Thanks

相关阅读:
Top