CODEWITHSUNDEEP
sqldatabasems-accessdml
Albert
Albert

Reputation: 29

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

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".

Upvotes: 1

Views: 1280

Answers (3)

Gustav
Gustav

Reputation: 55806

No need for a subquery:

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

Upvotes: 1

Muhammad Waheed
Muhammad Waheed

Reputation: 1088

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)

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below -

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

Upvotes: 1

Related Questions