Fill a field with the values from another field in MS Access

Question

I have a single table, Customers, and I want to delete/drop the field, FullName, and create two fields; FirstName and LastName respectively. Conveniently the names in the FullName only consist of two parts (e.g. John Smith, Robert Black). I have the MS Access compliant SQL code for extracting the first and last name from the FullName field below.

SELECT 
Mid(FullName, 1, InStr(FullName, ' ') - 1) 
AS FirstName, 
Mid(FullName, InStr(FullName, ' ') + 1, Len(FullName)) 
AS LastName 
FROM Customers 

I want to fill FirstName and LastName with the values from FullName, and then delete FullName. Below is my attempt to fill the FirstName field in DDL.

UPDATE Customers
SET Customers.FirstName =
(SELECT Mid(FullName, 1, InStr(FullName, ' ') - 1) )

The error I get from MS Access when I attempt to run this code is "Operation must use an updateable query".

Answer 1

You can try below -

UPDATE Customers
SET Customers.FirstName =Mid(FullName, 1, InStr(FullName, ' ') - 1)

Answer 2

No need for a subquery:

UPDATE 
    Customers
SET 
    FirstName = Mid(FullName, 1, InStr(FullName, " ") - 1),
    LastName = Mid(FullName, InStr(FullName, " ") + 1),
    FullName = Null

Answer 3

Try this. No need to use sub-query for same table columns and alias as well.

UPDATE Customers
SET  FirstName =Mid(FullName, 1, InStr(FullName, ' ') - 1)