Convert SELECT to UPDATE query

[SinCity]

I am trying to convert this SELECT query into an UPDATE query...

SELECT Left([BFirstName2],InStr([BFirstName2]," ")) AS BFirstName,
Mid([BFirstName2],InStr([BFirstName2]," ")+1) AS BLastName
FROM [77777 Show];

I tried simply replacing SELECT with UPDATE which is what I thought was
needed to be done. What am I missing?

What I am trying to do is UPDATE the data in table 77777 with the results of
the SELECT query.

Help!

 

[Van T. Dinh]

No ... you need the SET Clause in the UPDATE Query like

UPDATE [MyTable]
SET [MyField] = [Myfield] * 2

(meaning doubling the value of [MyField])

 

[Douglas J. Steele]

UPDATE [77777] INNER JOIN [77777 Show]
ON [77777].ID = [77777 Show].ID
SET BFirstName = Left([77777 Show].[BFirstName2],InStr([77777
Show].[BFirstName2]," "))
BLastName = Mid([77777 Show].[BFirstName2],InStr([77777
Show].[BFirstName2]," ")+1)

This assumes that the Primary Key for both tables is named ID. You'll have
to fuss with the ON clause if that's not the case.

 

[SinCity]

UPDATE [77777] INNER JOIN [77777 Show]
ON [77777].ID = [77777 Show].ID
SET BFirstName = Left([77777 Show].[BFirstName2],InStr([77777
Show].[BFirstName2]," "))
BLastName = Mid([77777 Show].[BFirstName2],InStr([77777
Show].[BFirstName2]," ")+1)

This assumes that the Primary Key for both tables is named ID. You'll have
to fuss with the ON clause if that's not the case.

Thanks so much Doug. I tried your suggestion but got the following error...

"Syntax Error in JOIN Operation".

Here is my exact code. Note that I changed the name of the table from "7777
Show" to "73106".

UPDATE [73106] INNER JOIN [73106]
ON [73106].ID = [73106].ID
SET BFirstName = Left([73106].[BFirstName2],InStr([73106].[BFirstName2],"
"))
BLastName = Mid([73106].[BFirstName2],InStr([73106].[BFirstName2]," ")+1)

Any idea what is wrong?

 

[Van T. Dinh]

Since you have only 1 Table, you don't need the join. try:

========
UPDATE [73106]
SET BFirstName = Left( [BFirstName2], InStr([BFirstName2]," ") ),
BLastName = Mid( [BFirstName2], InStr([BFirstName2]," ")+1 )
========