September 12, 2008 at 1:06 pm
I need to find values which are NULL, take the max value of the same column and update the null values the max value +1
I seem to be struggling.. ( I've forgotten )
Maybe you awesome people could throw me a cookie agian.
September 12, 2008 at 1:41 pm
perhaps if you'd give us a bit of the business reason for doing this we might be able to suggest a better way. Also, is this a one time data update or something that will be done on a more regular basis?
-Luke.
September 12, 2008 at 1:43 pm
also, is this something where you for each row you need the new max() value or if you have 10 rows and 2 are blank and the others are listed 1-8 would you be looking for 9 and 10 or 9 and 9?
Are you basically looking to create a "autonumber" column? If so why not look into an identity column.
-Luke.
September 12, 2008 at 2:55 pm
Well, I have to manipulate them somewhat.
Right now I update it 1 by 1 which is boring....
so I want to just run a script to do it for me but i've tried several ways without any luck ( usually resulting in renaming all nulls 1 number )
September 12, 2008 at 9:33 pm
Here's a script I use to do a similar task, I'm not doing any updates, but the selects are quite easy to follow.
Let me know if you have any questions.
DECLARE @ColumnsOrder TABLE
(
TableID INT NOT NULL,
ColName SYSNAME NOT NULL,
CurrentColid SMALLINT NOT NULL,
CorrectColid SMALLINT NOT NULL,
PRIMARY KEY ( TableId, ColName )
)
INSERT INTO @ColumnsOrder
(
TableID,
ColName,
CurrentColid,
CorrectColid
)
SELECT IK.id,
C.Name,
C.colid AS ActualPosition,
IK.Keyno AS CorrectPosition
FROM Sys.SysObjects O
INNER JOIN Sys.SysIndexes I ON O.Name = I.Name
INNER JOIN Sys.SysIndexKeys IK ON I.id = IK.id
AND I.Indid = IK.Indid
INNER JOIN Sys.SysColumns C ON I.id = C.id
AND IK.Colid = C.Colid
WHERE O.XType = 'PK'
INSERT INTO @ColumnsOrder
(
TableID,
ColName,
CurrentColid,
CorrectColid
)
SELECT C1.id,
C1.Name,
C1.Colid AS ActualPosition,
ROW_NUMBER() OVER ( PARTITION BY C1.id ORDER BY C1.Name )
+ ISNULL(dtPkColids.LastPkColid, 0) AS PKOffSet
FROM Sys.SysObjects O
INNER JOIN Sys.SysForeignKeys F ON O.id = F.constid
INNER JOIN Sys.SysColumns C1 ON F.FKeyId = C1.id
AND F.fkey = C1.colid
LEFT OUTER JOIN ( SELECT TableID,
MAX(CorrectColid) AS LastPkColid
FROM @ColumnsOrder
GROUP BY TableID
) dtPkColids ON C1.id = dtPkColids.TableID
LEFT OUTER JOIN ( SELECT IK.id,
C.Colid
FROM Sys.SysObjects O
INNER JOIN Sys.SysIndexes I ON O.Name = I.Name
INNER JOIN Sys.SysIndexKeys IK ON I.id = IK.id
AND I.Indid = IK.Indid
INNER JOIN Sys.SysColumns C ON I.id = C.id
AND IK.Colid = C.Colid
WHERE O.XType = 'PK'
) DtPkCols ON DtPkCols.id = C1.id
AND DtPkCols.Colid = C1.Colid
WHERE O.xType = 'F'
AND DtPkCols.id IS NULL --flush out the PK columns
INSERT INTO @ColumnsOrder
(
TableID,
ColName,
CurrentColid,
CorrectColid
)
SELECT C.id AS Tableid,
C.Name AS ColName,
C.Colid,
ROW_NUMBER() OVER ( PARTITION BY C.id ORDER BY C.Name )
+ ISNULL(dtPkColids.LastPkColid, 0) AS PKOffSet
FROM Sys.SysColumns C
LEFT OUTER JOIN @ColumnsOrder CO ON C.id = CO.TableID
AND C.Colid = CO.CurrentColid
LEFT OUTER JOIN ( SELECT TableID,
MAX(CorrectColid) AS LastPkColid
FROM @ColumnsOrder
GROUP BY TableID
) dtPkColids ON C.id = dtPkColids.TableID
WHERE CO.CurrentColid IS NULL
AND OBJECTPROPERTY(C.id, 'IsMsShipped') = 0
AND OBJECTPROPERTY(C.id, 'IsUserTable') = 1
--Column order validation
IF EXISTS ( SELECT *
FROM @ColumnsOrder
WHERE CurrentColid <> CorrectColid )
BEGIN
--SET @DesignIsValid = 0
SELECT 'Column order Validation (PK, in the PK Order, FK by Alphabetical Order, then alphabetical order for the rest of the columns.' AS Error,
DtErrors.TableName,
CO.ColName,
CO.CurrentColid,
CO.CorrectColid
FROM ( SELECT TableID,
OBJECT_NAME(TableID) AS TableName
FROM @ColumnsOrder
WHERE CurrentColid <> CorrectColid
GROUP BY TableID
) AS DtErrors
INNER JOIN @ColumnsOrder CO ON DtErrors.TableID = CO.TableID
ORDER BY DtErrors.TableName,
CO.CorrectColid
PRINT 'Failed test : Column order Validation (PK, in the PK Order, FK by Alphabetical Order, then alphabetical order for the rest of the columns'
END
September 12, 2008 at 9:34 pm
P.S. Replace all of the smileys byt a closing parenthesis " ) " when pasting the code.
September 13, 2008 at 11:52 am
rsheppick (9/12/2008)
Well, I have to manipulate them somewhat.
Not quite the business reason we were hoping for. 😉 There are several solutions depending on the requirements of a valid business reason, which you haven't yet given.
For example, if the numbers can, indeed, be updated in a relatively non-caring fashion, why not just drop the column and add a new column with the IDENTITY property? That would also keep you from ever having to worry about null values in that column ever again, as well.
If it's just for a tempory situtation, you don't even have to do that... just do a SELECT INTO a temp table using the IDENTITY function and "manipulate them somewhat".
To solve the problem exactly as you described it (I'd rather you posted the "WHY" of of your desired manipulation because there's probably a better way to solve your need), you can do this... it's VERY fast and, as you can see... VERY short....
DECLARE @CurrentMax INT
SELECT @CurrentMax = MAX(yourcolumn) FROM dbo.yourtable
UPDATE dbo.yourtable
SET @CurrentMax = yourcolumn = @CurrentMax + 1
WHERE yourcolumn IS NULL
For better and faster help on things like this, you could make it a bit easier for folks to help you by doing the things identified in the link in my signature below. 🙂
--Jeff Moden
September 16, 2008 at 1:23 am
Jeff Moden (9/13/2008)
rsheppick (9/12/2008)
Well, I have to manipulate them somewhat.Not quite the business reason we were hoping for. 😉 There are several solutions depending on the requirements of a valid business reason, which you haven't yet given.
For example, if the numbers can, indeed, be updated in a relatively non-caring fashion, why not just drop the column and add a new column with the IDENTITY property? That would also keep you from ever having to worry about null values in that column ever again, as well.
If it's just for a tempory situtation, you don't even have to do that... just do a SELECT INTO a temp table using the IDENTITY function and "manipulate them somewhat".
To solve the problem exactly as you described it (I'd rather you posted the "WHY" of of your desired manipulation because there's probably a better way to solve your need), you can do this... it's VERY fast and, as you can see... VERY short....
DECLARE @CurrentMax INTSELECT @CurrentMax = MAX(yourcolumn) FROM dbo.yourtable
UPDATE dbo.yourtable
SET @CurrentMax = yourcolumn = @CurrentMax + 1
WHERE yourcolumn IS NULL
For better and faster help on things like this, you could make it a bit easier for folks to help you by doing the things identified in the link in my signature below. 🙂
This is the reason why you are or you should be the SQL SERVER MVP ...yayaya :hehe::hehe::hehe:
Jeff, this code works perfect!
September 16, 2008 at 6:16 pm
Dugi (9/16/2008)
This is the reason why you are or you should be the SQL SERVER MVP ...yayaya :hehe::hehe::hehe:Jeff, this code works perfect!
Heh... thanks for the awesome feedback, Dugi.
Still, I wouldn't mind knowing the real reason the OP needs to do this.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply