| Author |
Topic |
|
jszulc
Yak Posting Veteran
66 Posts |
 Posted - 2010-05-04 : 15:44:33
|
| How do I write UPDATE statement to update all PropertyID records that are displaying less than 6 characters to 6 characters. Ex I have 1000 records that are showing as 92317. I need this record to be 092317. I have 200 records that are showing as 672. I would need to flip them to 000672. The problem is I have thousands of them so I cannot do them individually. Is tehre a way to do this in bulk with 1 statemnet on the entire column?CREATE TABLE [dbo].[KPITbl]( [TaskID] [int] IDENTITY(1,1) NOT NULL, [Lead] [nvarchar](255) NULL, [WRM] [nvarchar](255) NULL, [PropertyIDStartLoc] [nvarchar](250) NULL |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jszulc
Yak Posting Veteran
66 Posts |
Posted - 2010-05-04 : 16:23:02
|
| (42579 row(s) affected)however did not work..... Any ideas? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-04 : 16:28:25
|
| Need to add a where clause: WHERE DATALENGTH(PropertyIDStartLoc) < 12 -- 6*2 since using nvarcharHere's my test:CREATE TABLE #KPITbl (PropertyIDStartLoc nvarchar(50))INSERT INTO #KPITbl VALUES ('1234')INSERT INTO #KPITbl VALUES ('12345')INSERT INTO #KPITbl VALUES ('123')INSERT INTO #KPITbl VALUES ('12')INSERT INTO #KPITbl VALUES ('1234567')INSERT INTO #KPITbl VALUES ('123456')SELECT * FROM #KPITblUPDATE #KPITblSET PropertyIDStartLoc = RIGHT('000000' + PropertyIDStartLoc, 6)WHERE DATALENGTH(PropertyIDStartLoc) < 12 -- 6*2 since using nvarcharSELECT * FROM #KPITblDROP TABLE #KPITblTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jszulc
Yak Posting Veteran
66 Posts |
Posted - 2010-05-04 : 16:52:37
|
| still no luck |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-04 : 17:03:58
|
| As illustrated with sample data, Tara's solution should give your desired results.Can you please show some sample data from your table and the exact query you tried. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2010-05-05 : 01:59:58
|
| Trigger ,do you have a trigger on this table? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-05-07 : 07:22:47
|
| Did you run it on a character column?Otherwise you need to convert it to varcharMadhivananFailing to plan is Planning to fail |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-07 : 07:37:19
|
quote:
Originally posted by tkizer
Need to add a where clause: WHERE DATALENGTH(PropertyIDStartLoc) < 12 -- 6*2 since using nvarchar
This is VERY important, since without it, the value 1234567 will be updated to 234567------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
UPDATE in bulk