Newbie - How to update multiple rows with select statment

Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

PamI am disappointed that all the experts here have no time for my basic question ...|||you need to write a query like

update a
set a.col = b.col
from yourtbl a, (your select query) b
where a.primarykey = b.primarykey|||Thanks for the reply.

Thats too basic. Are you suggesting me to create new table and then associate the values as you quoted above ??|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0|||I'd suggest:DROP TABLE Pam24
GO

CREATE TABLE Pam24 (
Pam24id INT IDENTITY
CONSTRAINT XPKPam24
PRIMARY KEY (Pam24id)
, thingie VARCHAR(50) NULL
)

INSERT INTO Pam24 (thingie)
SELECT '123 Main Street' UNION ALL
SELECT NULL UNION ALL
SELECT '456Any Road' UNION ALL
SELECT '' UNION ALL
SELECT '789 My Place'

UPDATE Pam24
SET thingie = Left(thingie, PatIndex('%[^0-9]%', thingie) - 1)
WHERE thingie LIKE '[0-9]%'

SELECT *
FROM Pam24Note that the two patterns are different, in fact exact opposites. I dislike having two patterns, but it was better than any alternative I could think of on short notice.

-PatP|||pat, nice try, but if the string does not start with a number, the original spec (yes, i realize it's buried inside some non-functional sql) required that you reset the entire value to 0

:)|||actually, now that i look at it more closely, the 0 was actually the length parameter of the substring function, so i think maybe it's supposed to reset all alpha-only strings to empty strings|||enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

here's my solution (note: i tested it, including all alpha and all numeric column values) --update GOLDIE
set GOLDIE_ID =
case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
then left(GOLDIE_ID
,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
else 0 end
where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0

Thanks r937. It does answers my question.

Cheers