SQL UPDATE DUPLICATE DATA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL UPDATE DUPLICATE DATA

Author

Topic

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 08:13:33

Hello,
want to change duplicate data in my sql server colunm.
the data= 'Barack Obama' , what I want
'Barack Obama 23453'

I have the following query:


UPDATE klant SET naamvoornaam=naamvoornaam  + CAST(klantnummer AS VARCHAR)
WHERE naamvoornaam NOT IN (
SELECT(naamvoornaam)
FROM klant
GROUP BY naamvoornaam
HAVING ( COUNT(naamvoornaam) = 1 ))

Message from messages:
String or binary data would be truncated.

I think the max lenght exceeds the lenght of the
destination column.
I've tried:


Max(Len(naamvoornaam  + CAST(klantnummer AS VARCHAR)))

message from messages:
An aggregate may not appear in the set list of an UPDATE statement.

Some help?

if you do not try, it will not work

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-17 : 08:18:00

yes that is what the error says,you are trying to insert more data than the length of the column in destination.

please post the table structure

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 08:20:07

nvarchar(40)

I've tried:

Max(Len(naamvoornaam + CAST(klantnummer AS VARCHAR)))

message from messages:
An aggregate may not appear in the set list of an UPDATE statement.

if you do not try, it will not work

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 08:32:14

[code]
id123 dbo Klant Klantnummer 1 NO int 10 10 0
id123 dbo Klant Gezinsbond 2 ((0)) NO bit
id123 dbo Klant Naamvoornaam 3 YES nvarchar 40 80 UNICODE Latin1_General_CI_AS
id123 dbo Klant Adres 4 YES nvarchar 40 80 UNICODE Latin1_General_CI_AS
id123 dbo Klant Huisnummer 5 YES nvarchar 15 30 UNICODE Latin1_General_CI_AS
id123 dbo Klant GemeenteId 6 ((0)) YES int 10 10 0
id123 dbo Klant Telefoonnummer 7 YES nvarchar 15 30 UNICODE Latin1_General_CI_AS
id123 dbo Klant GSMnummer 8 YES nvarchar 15 30 UNICODE Latin1_General_CI_AS
id123 dbo Klant Email 9 YES nvarchar 50 100 UNICODE Latin1_General_CI_AS
id123 dbo Klant Korting 10 ((5)) NO smallint 5 10 0
id123 dbo Klant DatumInProgramma 11 (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))) YES datetime 3
id123 dbo Klant KlantMaat 12 YES nvarchar 75 150 UNICODE Latin1_General_CI_AS
id123 dbo Klant KlantAankoop 13 YES nvarchar 30 60 UNICODE Latin1_General_CI_AS
id123 dbo Klant Foutief adres 14 YES nvarchar 20 40 UNICODE Latin1_General_CI_AS
id123 dbo Klant txtKortingBdr 15 ((0)) YES real 24 2
id123 dbo Klant TeGoed 16 ((0)) YES decimal 6 10 2
id123 dbo Klant GeschenkbonSaldo 17 ((0)) YES real 24 2
[/code]

if you do not try, it will not work

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-17 : 08:38:47

I can only say, you need to increase length of the column to varchar(50)
Javeed Ahmed

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 08:42:41

Ok I try to do that. Txs.

Do you know how I put a space between
the name and the numbers .
not 'Barack Obama1234'
but 'Barack Obama 1234'


UPDATE klant SET naamvoornaam=naamvoornaam  + CAST(klantnummer AS VARCHAR)

if you do not try, it will not work

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-10-17 : 08:45:46

can you try this
UPDATE klant SET naamvoornaam=naamvoornaam +' '+CAST(klantnummer AS VARCHAR)

Javeed Ahmed

JohnDW
Starting Member

45 Posts

Posted - 2014-10-17 : 09:27:32

TXS!

if you do not try, it will not work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources