Correlated Subqueries
Morning a colleague asked me a SQL statement how to update rows in a table base on other from another table.So easy.
My answer is "update emp_salary es set es.subsidy1 = (select worktime * 10 from employee emp where emp.empid = es.empid and emp.worktime > 10)".
What Is a Subquery?
A subquery is a SELECT statement embedded in a clause of another SQL statement.
For Example "select * from employee emp where empid in (select emp from salary where tax > 1000)" . in this statement "select emp from salary where tax > 1000" is the subquery embedded in the main statement .
Correlated subqueries are useful whenever a subquery must return a different result for each candidate row.[from Extended Data Retrieval with SQL by Oracle]
With Exists operator you can return boolean value;
some point:
1、Subquery(inner query) excutes once before the main query(outer query)(once of every row of the outer query).
2、The result of the subquery is used by the main query.
Morning a colleague asked me a SQL statement how to update rows in a table base on other from another table.So easy.
My answer is "update emp_salary es set es.subsidy1 = (select worktime * 10 from employee emp where emp.empid = es.empid and emp.worktime > 10)".
What Is a Subquery?
A subquery is a SELECT statement embedded in a clause of another SQL statement.
For Example "select * from employee emp where empid in (select emp from salary where tax > 1000)" . in this statement "select emp from salary where tax > 1000" is the subquery embedded in the main statement .
Correlated subqueries are useful whenever a subquery must return a different result for each candidate row.[from Extended Data Retrieval with SQL by Oracle]
With Exists operator you can return boolean value;
some point:
1、Subquery(inner query) excutes once before the main query(outer query)(once of every row of the outer query).
2、The result of the subquery is used by the main query.
回复Comments
{commenttime}{commentauthor}
{CommentUrl}
{commentcontent}