CODEWITHSUNDEEP
sql-server
Yki_Summer
Yki_Summer

Reputation: 27

Update in multiples rows

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.

Upvotes: 0

Views: 60

Answers (3)

Ponmani Chinnaswamy
Ponmani Chinnaswamy

Reputation: 865

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

Upvotes: 0

Nfyfr Qwertyuio
Nfyfr Qwertyuio

Reputation: 1

Return one row 

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

Upvotes: 0

Dmitry Rotay
Dmitry Rotay

Reputation: 3280

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

Upvotes: 1

Related Questions