Update in multiples rows

Question

How can I write an UPDATE when I have a sub query?

Take a look:

UPDATE People 
SET Name = dbo.formatt_name((SELECT Name 
 FROM People 
 WHERE idInstitute = 12)) 
WHERE idInstitute = 12

I've created a function formatt_name(str) that returns a formatted string.

I would like to update all names on my table using that function, someone knows how can I get this ?

I get this error message:

Msg 512, Level 16, State 1.

I know, just one result for set the update. But, I have no idea how to solve this.

• Ponmani Chinnaswamy: Here no need of subquery. You are using same where condition in subquery.
• Yki_Summer: I've to change all names, so the subquery is returning all names needing to change.
• Ponmani Chinnaswamy: You have to update all rows means you just add query without condition. UPDATE People SET Name = dbo.formatt_name(Name)
• Yki_Summer: I want to update only the names of people that belongs to institute = 12.

Answer 1

Why do you use subquery? You should be able to update rows like this:

UPDATE People
SET Name = dbo.formatt_name(Name) 
WHERE idInstitute = 12

• Yki_Summer: Dmitry all datas are in the database, i need a query to change all of them
• Dmitry Rotay: This query will update names of ALL people which have idInstitute = 12 with value computed by dbo.formatt_name function. Isn't this what you're trying to do actually? You should try it, check the result and tell us what's wrong, if it is.
• Yki_Summer: Does't work because the parameter 'name' must to be a value returned by another query. this subquery returns some values, so i got that error on Sql Server. I'm trying. Do you have another idea ? Thanks for helping
• Dmitry Rotay: Then you need to make sure than another query returns ONLY ONE row and you will be ok. Like, add a more specific WHERE clause to the query inside (). Can you write such a query? Because idInstitute = 12 is obviously not the kind of condition that will be true for only one row.
• Yki_Summer: ok. I've a database and all clients names is there. I've to make a script that correct all names. My function works , but only one result. When I'm using tha expression doesn't works. There's some possibility to do that without a line to line, manually...?

Answer 2

As per your comments, you have to update all records of the table where peoples belongs to institute =12 and add another condition Name is not null also .

UPDATE People SET Name = dbo.formatt_name(Name) where 
idInstitute=12 and Name IS NOT NULL 

Edited:

From my understanding you have to format all names in each record.

 idinstitute Name 
 12 Antony | Jhon | Cris | Peter
 12 kEvin| JhOn | antony | PCrIS

here you no need of subquery. find the sample function for the formatted string.

CREATE FUNCTION [dbo].[formatt_name] (@InputString VARCHAR(max) )
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString =
STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF @Char != ''''
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET @Index = @Index + 1
END
RETURN ISNULL(@OutputString,'')
END

and query for update,

 UPDATE People SET Name = dbo.formatt_name(Name) where 
 idInstitute=12 and Name IS NOT NULL 

enter image description here

• Yki_Summer: Pnomani, the name must to be a subquery to get all the names. I don't want to turn all the names de same. thank you for reply... but doesn't work
• Ponmani Chinnaswamy: what is your expectation result ? can you give some example
• Yki_Summer: ok, some table have 4 names : antony | JhOn | CrIS | PETER The result must to be : Antony | Jhon | Cris | Peter
• Ponmani Chinnaswamy: @Summerson , i have edited the post. please find which satisfies your expectation.
• Yki_Summer: thank you man, this worked , I should pass the table name as a parameter. Thank you so much. The problem now is the function get all date from table, but this i can fix . Thank you !!!!

Answer 3

Return one row

UPDATE PEOPLE p
SET p.Name = dbo.formatt_name(p.Name)
WHERE p.id = p.id and p.idInstitute = 12

• Arulkumar: Instead of a new answer, you can update the same content in your existing answer.
• Yki_Summer: Nfyfr , as I sai bellow, i need to get this result : some table have 4 names : antony | JhOn | CrIS | PETER The result must to be : Antony | Jhon | Cris | Peter