March 25, 2014 at 6:05 am
Hi,
I have a column, lastName and I wish to update the data so all lastName's are renamed apart from certain ones.
lastName (now) lastName (updated)
----------------- ----------------------
Clooney lastName1
Bobbins lastName2
Smith Smith
Jones lastName3
So in other words working through the rows and updating the lastName incrementally but ignoring the row when the lastName is Smith.
any help, greatly appreciated, thanks
March 25, 2014 at 6:23 am
Something like this:
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Names')
DROP TABLE Names
GO
CREATE TABLE Names (
PKID INT IDENTITY(1,1),
LastName nvarchar(100)
)
GO
INSERT INTO Names (LastName)
SELECT 'Jones' UNION ALL
SELECT 'Smith' UNION ALL
SELECT 'Taylor' UNION ALL
SELECT 'David' UNION ALL
SELECT 'McCann' UNION ALL
SELECT 'Smith'
SELECT * FROM Names
;WITH CTE_UPDATES AS (
SELECTPKID,
LastName,
'Lastname' + CAST(ROW_NUMBER() OVER (ORDER BY PKID ASC) as NVARCHAR(100)) AS NewName
FROM Names
WHERE LastName <> 'Smith')
UPDATE A
SET A.LastName = B.NewName
FROM Names A
JOIN CTE_UPDATES B
ON A.PKID = B.PKID;
SELECT * FROM Names
MCITP SQL 2005, MCSA SQL 2012
March 25, 2014 at 7:02 am
Just to throw my hat into the ring, this can be done with a single scan of the table by updating the table in the cte directly. It can also be done without a reference to the PK if that's not important.
--===== Create a test table and populate it
IF OBJECT_ID('temdb..#Names','U') IS NOT NULL
DROP TABLE #Names
;
SELECT LastName = CAST(d.LastName AS VARCHAR(100))
INTO #Names
FROM (
SELECT 'Jones' UNION ALL
SELECT 'Smith' UNION ALL
SELECT 'Taylor' UNION ALL
SELECT 'David' UNION ALL
SELECT 'McCann' UNION ALL
SELECT 'Smith'
) d (LastName)
;
SELECT * FROM #Names
;
--===== Do the update without a PK reference
WITH
cteEnumerate AS
(
SELECT LastName,
NewLastName = 'Lastname' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(100))
FROM #Names
WHERE LastName <> 'Smith'
)
UPDATE cteEnumerate
SET LastName = NewLastName
;
SELECT * FROM #Names
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2014 at 7:19 am
They both do the job thanks BUT I forgot to mention I wanted to select DISTINCT lastName's, is that possible using a cte?
March 25, 2014 at 7:25 am
So all lastnames that are the same will get the same incremental number?
I.e. All surnames of Jones become Lastname1 and all surnames of Taylor get LastName2
MCITP SQL 2005, MCSA SQL 2012
March 25, 2014 at 8:43 am
yep
March 25, 2014 at 8:47 am
here you go, I'll let Jeff update his solution:
**** removed my code *** that didn't work. . Will post back
MCITP SQL 2005, MCSA SQL 2012
March 25, 2014 at 9:09 am
ok 🙂
March 25, 2014 at 9:32 am
Using a combination of Jeff and I's solution, you can replace DENSE_RANK() WITH RANK() should you not mind gaps in the ordering when you have more than 1 instance of the same name.
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Names')
DROP TABLE Names
GO
CREATE TABLE Names (
PKID INT IDENTITY(1,1),
LastName nvarchar(100)
)
GO
INSERT INTO Names (LastName)
SELECT 'Jones' UNION ALL
SELECT 'Smith' UNION ALL
SELECT 'Taylor' UNION ALL
SELECT 'David' UNION ALL
SELECT 'Taylor' UNION ALL
SELECT 'Zaine' UNION ALL
SELECT 'Wee' UNION ALL
SELECT 'Smith'
SELECT * FROM Names
;WITH CTE_UPDATES AS (
SELECTLastName,
'LAStName' + CAST(DENSE_RANK() OVER (ORDER BY LASTname ASC) AS NVARCHAR(100)) AS NewName
FROM Names
WHERE LastName <> 'Smith')
--SELECT * FROM CTE_UPDATES
UPDATE CTE_UPDATES
SET LastName = NewName
SELECT * FROM Names
MCITP SQL 2005, MCSA SQL 2012
March 25, 2014 at 10:44 am
That's great, thanks guys.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply