Self Join Query


Self Join

SQL Self Join Example

Access self join:  In this update query example we use the self join query technique to update each employee’s salary by 10%.  There are at least two ways to do this update.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_Emp as A INNER JOIN M_Emp as B ON A.Emp_ID = B.Emp_ID SET A.Salary = B.Salary*1.1

b)  Update M_Emp as A, M_Emp as B Set A.Salary=B.Salary * 1.1
Where A.Emp_ID=B.Emp_ID

c)  Update M_Emp as A set A.Salary = (Select Salary * 1.1 from M_Emp Where M_Emp.Emp_ID = A.Emp_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 One Table/Same Table Update Examples:

Self Join in Visual Basic Detail Master Update

Table Data in Access

Access Self Join Query for Inventory Calculations Example

Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365


Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016