sql - Update Table with value from another table, else NULL

Question

I was trying to Update a query on MS-Access. Let's Assume, I have two Tables (Employee, Department). I have (ID, Name, FK_DepartmentID) in Employee Table. And, two Column in Department(ID, DepartmentName) Table, Where I want to check if department Name exists in Department Table, then Update in Employee.FK_DepartmentID, else Null. Currently, I am updating with

UPDATE Employee, Department SET 
Employee.Name = 'David', 
Employee.FK_DepartmentID = Department.ID 
WHERE (((Employee.ID)='55') AND ((Department.DepartmentName)='HR');

So this is updating my Employee Table. But Let's say User input Department.DepartmentName='IT', where 'IT' doesn't exist in Department Table (Or User input nothing). With the current query, it executes but doesn't update any row. But I want it to update Employee.Name alongside Employee.DepartmentID = Null. What can I do so I can reach what I want.

Really appreciate your time and thanks in advance.

question from:https://stackoverflow.com/questions/65557387/update-table-with-value-from-another-table-else-null

Answer 1

What you are describing is an upsert, which is short for update or insert based on a select. you can find plenty more examples using the keywords ACCESS and Upsert. Access doesn't have special syntax for upserts. You have to write the select, update, insert, and logic yourself. I haven't found a way to get around using VBA for upserts in Access as you have to call a select query then choose either to update or insert. My example ended up being with strings but it is otherwise similar to what I think you are trying to do. So assuming a one to many relationship between departments and employees:

Here is the code be careful to notice that the strings are enclosed in ' ':

Public Sub UpsertEmployee(EmployeeName As String, departmentName As String)
Dim db As dao.Database
Set db = CurrentDb
Dim sqlstring As String
Dim employeeID As Integer
Dim departmentID As Integer
employeeID = SelectEmployee(EmployeeName)
departmentID = SelectDepartment(departmentName)
If employeeID = -1 And departmentID = -1 Then 'no employeee or department insert employeename with null department'

sqlstring = "INSERT INTO Employees (Name, FK_DepartmentID) VALUES( '" & EmployeeName & "', NULL)"

db.Execute (sqlstring)
ElseIf employeeID > 0 And departmentID > 0 Then 'got both update both'

sqlstring = "UPDATE Employees SET Name = '" & EmployeeName & "', FK_DepartmentID = " & departmentID & " WHERE ID = " & employeeID

db.Execute (sqlstring)
ElseIf employeeID > 0 And departmentID = -1 Then 'got employee and no department update department to null'
sqlstring = "UPDATE Employees SET Name = '" & EmployeeName & "', FK_DepartmentID = NULL" & " WHERE ID = " & employeeID
db.Execute (sqlstring)
ElseIf employeeID = -1 And departmentID > 0 Then 'no employee so insert and set department to correct id'
sqlstring = "INSERT INTO Employees (Name, FK_DepartmentID) VALUES( '" & EmployeeName & "', " & departmentID & ")"
db.Execute (sqlstring)
End If
db.Close
Set db = Nothing
End Sub

Public Function SelectDepartment(departmentName As String) As Integer
'normally I would give users a combobox to select department avoiding this problem entirely'
Dim db As dao.Database
Set db = CurrentDb
Dim rs As dao.Recordset
Dim sqlstring As String
sqlstring = "SELECT ID FROM Departments WHERE DepartmentName Like '" & departmentName & "'"
Set rs = db.OpenRecordset(sqlstring)
If rs.RecordCount > 0 Then 'no records so must insert'
rs.MoveFirst
SelectDepartment = rs(0)  'only returning first record here for simplicity'
Else
SelectDepartment = -1 '-1 is an impossible autonumber were using to indicate no record found'
End If
Set rs = Nothing
db.Close
Set db = Nothing
End Function
Public Function SelectEmployee(EmployeeName As String) As Integer
Dim db As dao.Database
Set db = CurrentDb
Dim rs As dao.Recordset
Dim sqlstring As String
sqlstring = "SELECT ID FROM Employees WHERE Name = '" & EmployeeName & "'"
Set rs = db.OpenRecordset(sqlstring)
If rs.RecordCount > 0 Then
rs.MoveFirst
SelectEmployee = rs(0)  'note employees with same name are a problem, etc; this is just an example'
Else
SelectEmployee = -1
End If
'clean up'
Set rs = Nothing
db.Close
Set db = Nothing
End Function