How to update multiple rows with select statement referencing same table

Question

I have a table, Salesperson, and the current name column shows the following:

Undefined - 0
Sam Brett-sbrett
Kelly Roberts-kroberts
Michael Paramore-mparamore
Alivia Lawler-alawler
Ryan Hooker-rhooker
Heather Alford-halford
Cassandra Blegen-cblegen
JD Holland-jholland
Vendor Accounts-VENDOR
Other Accounts-OTHER
Rose Johnson-rjohnson

I've developed the following query to extract the name (minus the ID on the end) with which I want to update this column:

SELECT left(salesperson_name, CHARINDEX('-', salesperson_name) - 1)
FROM Salesperson
------------------------
Undefined 
Sam Brett
Kelly Roberts
Michael Paramore
Alivia Lawler
Ryan Hooker
Heather Alford
Cassandra Blegen
JD Holland
Vendor Accounts
Other Accounts
Rose Johnson

I have attempted numerous variations of the following, but get the understandable

Subquery returned more than 1 value...' error.

begin tran
update salesperson
set salesperson_name = (SELECT left(salesperson_name, CHARINDEX('-', salesperson_name) - 1)
FROM Salesperson)

The issue is that your subquery isn't correlated with the outer query, hence it processes the entire Salesperson table for each row in the table. — HABO
– HABO, Commented Jun 29, 2019 at 12:23

ScaisEdge · Accepted Answer · 2019-06-28 19:30:23Z

3

why not a simple

update salesperson
set  salesperson_name  = left(salesperson_name, CHARINDEX('-', salesperson_name) - 1)

answered Jun 28, 2019 at 19:30

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update multiple rows with select statement referencing same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related