CODEWITHSUNDEEP
sqlms-access
Alex
Alex

Reputation: 6645

Copying SELECT data into columns in same table

If have a SELECT statement that is designed to take an existing column and split it into two new ones:

SELECT IIf(InStr(1,[First Name]," ")>0,Left([First Name],InStr(1,[First Name]," ")),[First Name]) AS Fname, 
IIf(InStr(1,[First Name]," ")>0,Mid([First Name],InStr(1,[First Name]," ")+1),NULL) AS Mname
FROM Pupils;

I've added the columns Fname and Mname to the table. How can I copy the data returned from the SELECT into the new columns on the same table?

Upvotes: 0

Views: 46

Answers (2)

HansUp
HansUp

Reputation: 97101

Reuse those IIf expressions from your SELECT query in an UPDATE query.

UPDATE Pupils
SET
    Fname = IIf(
        InStr(1,[First Name]," ")>0,
        Left([First Name],InStr(1,[First Name]," ")),
        [First Name]
        ),
    Mname = IIf(
        InStr(1,[First Name]," ")>0,
        Mid([First Name],InStr(1,[First Name]," ")+1),
        NULL
        );

Upvotes: 1

Dgan
Dgan

Reputation: 10285

Update with Join

Update M
set M.NewCol1=D.Fname 
  M.NewCol2=D.Mname
from  MainTable M
JOIN
(
SELECT IIf(InStr(1,[First Name]," ")>0,Left([First Name],InStr(1,[First Name]," ")),[First Name]) AS Fname, 
IIf(InStr(1,[First Name]," ")>0,Mid([First Name],InStr(1,[First Name]," ")+1),NULL) AS Mname
FROM Pupils)D
ON M.col1 = D.col1

Upvotes: 0

Related Questions