Update a #table using where clause

Question

Post reply

Update a #table using where clause

SpeedSkaterFan

SSCrazy

Points: 2716
More actions
October 29, 2015 at 2:00 pm

#301814

Hi all,
I have a temporary table #Temp with, among others, a column CountryCode and a column Lastname
I would like to change the ü that appears in some names to u (u umlaut to a plain u), but only for those that have the nationality 'Ned'
My code so far:
Update #Temp
set LastName = replace(Lastname, 'ü', 'ue') WHERE CountryCode = 'Ned'
This code deletes all entries in the column Lastname
I think it must be easy, but I keep staring at the code.
What to do?
Thanks in advance
Hein

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I don't see the problem with your code. Probably if you give more details on the problem.

CREATE TABLE #Temp(

LastName varchar(10),

Countrycode char(3)

);

INSERT INTO #Temp

VALUES

('Müller','USA'),

('Gueller','USA'),

('Müller','Ned'),

('Gueller','Ned');

SELECT * FROM #Temp;

Update #Temp

set LastName = replace(Lastname, 'ü', 'ue') WHERE CountryCode = 'Ned';

SELECT * FROM #Temp;

DROP TABLE #Temp;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Further on Luis's fine answer, works fine and unless there is something else affecting the code that we don't know of, then there isn't much to do;-)

😎

You can add some extra where conditional such as detecting the presence of an umlaut

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TEMP_UPDATE') IS NOT NULL DROP TABLE dbo.TEMP_UPDATE;

CREATE TABLE dbo.TEMP_UPDATE

(

TUP_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TEMP_UPDATE_TUP_ID PRIMARY KEY CLUSTERED

,CountryCode VARCHAR(3) NOT NULL

,LastName NVARCHAR(50) NOT NULL

);

INSERT INTO dbo.TEMP_UPDATE(CountryCode,LastName)

VALUES ('Spa',N'James')

,('Ned',N'Jürgen')

,('Ned',N'Jorge')

,('Eng',N'Joe')

;

UPDATE TUP

SET TUP.LastName = REPLACE(TUP.LastName,N'ü',N'ue')

FROM dbo.TEMP_UPDATE TUP

WHERE TUP.CountryCode = 'Ned'

AND CHARINDEX(N'ü',TUP.LastName) > 0;

SELECT

TUP.TUP_ID

,TUP.CountryCode

,TUP.LastName

FROM dbo.TEMP_UPDATE TUP;

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

I don't see any problem with it either. I tried Luis's version of it with both varchar and nvarchar, and the update statement you posted works fine. I also tried it with an nchar and both with and without the Unicode version (N'ü' versus 'ü') and they all work.

I'm thinking there most be something else you're doing. Could you please fill in what we're missing?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 4

Hi Luis,

Thanks for your reply, and I must say: you can knock me over with a feather.

Sorry for asking this question. I tried to run run the code again and ... it worked! :blush:

I have no idea why it works all of a sudden, after many tries, and alternations, without succes.

Maybe I should have restarted my computer at some stage. Maybe some internal memory was full.

I don't know.

The important thing is that I have my correct outcome.

Thanks again

Hein

Ed and Eirikur,

You responded when I was typing my respons to Luis.

Thanks for contributions

I will use all code to study (always a good thing) and I have changes like this coming my way.

I will use the code than

Thanks

Hein

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Just a wild guess. You might have selected part of the update without the where clause and ran the query. That would have prevented the code to work as expected and update all rows.

Otherwise, I'm not sure what happened.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2