Update Text Field on Multiple Rows Using Current Text In Field SQL

I have a table with a text field that imported with incorrect characters delimited by a space.

---------------------
myTable:

id     text

1      text BADCHARACTERS
2      text2 DIFFERENTBADCHARACTERS
3      text3 ASDF
4      text4 VERYBADCHARACTERS
---------------------

Expected Output

---------------------
myTable:

id     text

1      text
2      text2
3      text3
4      text4
---------------------

I need to run an update to remove all the text AFTER the space on a specific number of rows. I figured out a way to select the correct text, but I’m stuck on how to update in bulk.

SELECT LEFT(text, CHARINDEX(' ', text) -1)
FROM myTable
WHERE ID = 1

Will return the correct string.

Any thoughts on how to update a specific set records with the respective text in each row?

Answer

You can put the logic in your select directly into an update:

update mytable
    set text = LEFT(text, CHARINDEX(' ', text) -1)
    where ID = 1 and text like '% %';