Self Join Query
SQL Self Join Query Example
We use a self join in this update query example we want to update each employee's salary by
10%. There are at least two ways to do this query.
Example c) (below) is supposed to work but I get errors in Access. It is called a correlated subquery - if you can tell me what is wrong with it I'd really appreciate it.
Self Join Query Code:
a) Update M_Employees as A INNER JOIN M_Employees as B
ON A.Employee_ID = B.Employee_ID SET A.Salary = B.Salary*1.1
b) Update M_Employees as A, M_Employees as B Set
A.Salary=B.Salary * 1.1
Where A.Employee_ID=B.Employee_ID
c) Update M_Employees as A set A.Salary = (Select Salary
* 1.1 from M_Employees Where M_Employees.Employee_ID = A.Employee_ID)
Note: I have added a another feature to this self join
query example
- Aliases. Aliases are where you use 'as' to establish a short nickname
for a table or a field. This alias feature helps when you have long table names or are trying to
do a correlated subquery.
Additional Update Query Information:
RunSql ("Update Tasks set
Task_Complete=False " & _ " where Task_ID=" & Me.Task_ID). End If. End
Sub. Ok... this is more of an SQL example than a visual ...
www.blueclaw-db.com/access_advanced_VB_scripts.htm |
c) Update M_Employees as A set
A.Salary = (Select Salary * 1.1 from M_Employees Where
M_Employees.Employee_ID = A.Employee_ID) ...
www.blueclaw-db.com/accessquerysql/same_table_updates.htm |
RunSQL "Update M_Inventory set
inv_qty=inv_qty+" & Nz(hold_qty, 0) & " where barcode=" & Me.BarCode
DoCmd.RunSQL "Update M_Inventory set inv_qty=inv_qty-"
...
www.blueclaw-db.com/download/download_inventory_calculations.htm |