September 26, 2009 at 9:56 am
I have to pad a field that falls short of 20 bytes. I will need to select all the records that meet the above criteria to perform this task. I do not want to use a cursor, I understand that this can be done with Set based operations.
Once I select the records how do I update all of the records on the table?
Thanx,
Freddye
September 26, 2009 at 1:08 pm
trudye10
Rather skimpy amount of information for someone to assist you. Can you please post the table definition, identify the specific column and its data type. Some sample data and what you expect as a result. In other words read the link in my signature block and complete what you can ... and remember it does not have to be actual company data, but representative data ...
September 26, 2009 at 1:57 pm
Trudye, since you are obviously a beginner, I will help you out a little on this one. BB is absolutely right, though. If you will put a little effort into setting up the problem, you will get correct and tested answers faster than just asking general questions.
SQL is designed to work on sets of data. This is true not only for SELECT statements, but INSERT, UPDATE, DELETE, and MERGE statements as well. Each of these statements can operate on a subset of rows in a table by means of JOINs and WHERE clauses just like a SELECT statement. For best performance, you need to change your mindset from procedural to set-based thinking when you work with SQL
Procedural thinking for Updates says:
1. I need to find the first row that meets certain criteria
2. I need to UPDATE that row
3. I need to loop back and find the next row that meets the same criteria
What most people don't understand is that SQL is prepared to do most of that for you, without any need to code a loop.
Set-based thinking says:
1. I need to UPDATE all rows that meet certain criteria.
In my next post am going to set up your problem the way we would like to see it presented in the future, because it is simple and because I have time on my hands this afternoon. Don't expect it to happen again, because setting up sample data should be YOUR job. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 26, 2009 at 2:14 pm
-- set up sample data (this will be your job next time)
declare @sample table (string varchar(40))
insert into @sample
select 'Apples' union all
select 'Bananas' union all
select 'Coconuts' union all
select 'Paradimethylaminobenzaldehyde'
-- view the set of rows that have a length less than 20
-- this SELECT is NOT necessary to the UPDATE which follows
SELECT string,len(string) as strLength
FROM @sample
WHERE len(string) < 20
-- update the table
UPDATE @sample
SET string = string+REPLICATE('-',20-len(string))-- Look up the REPLICATE function in books online
WHERE len(string) < 20-- Same where clause as the previous select, SQL does all the "looping"
-- show all the rows after the update, note the the last row was not updated
SELECT string,len(string) as strLength
FROM @sample
Although this example does a couple of SELECTS, the only statement you need to be concerned with is the UPDATE statement. It does a where clause to define the set of rows to be updated ( all rows where the string column has a length < 20 ). It uses the REPLICATE function to pad the rows, and it calculates how much to pad on a row-by-row basis. More sophisticated update logic can be built using the CASE expression, which is like a function containing IF logic.
Please let me know if you have any questions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 26, 2009 at 3:51 pm
Hi Guys thank all of you for responding so quickly (and on a Saturday too (smiel))!
I think I figured it out:
IF (SELECT COUNT(*) FROM Enroll_Dtl WHERE LEN(Card_Num) 16) > 0
BEGIN
INSERT INTO Enroll_Error_Tbl (RunDate,Card_Num, L_Name, F_Name, VendorName, Err_Description)
SELECT @Run_Date, Card_Num ,LName, FName, Vendor_Name, 'Card Number lt 16 bytes'
FROM Enroll_Dtl WHERE LEN(Card_Num) < 16
Update Enroll_Dtl
SET Card_Num = Right('0000000000000000' + Cast(Card_Num as varchar (16)), 16)
WHERE LEN(Card_Num) 0
END
OBTW, I was wrong I was suppose to pad to 16 bytes not 20.
Thanx again
September 26, 2009 at 4:05 pm
Good for you, Trudye. 🙂
Two hints for the future:
I notice you are saving "before" images of the rows which will be updated. You might want to read up on the OUTPUT clause in books online. OUTPUT was new in SQL 2005, and lets you trap inserted, updated, and deleted rows without having to make two passes through the primary table. Simply put, using UPDATE with the OUTPUT clause would get the job done for you in one pass, rather than the three passes in your solution.
You might also want to consider using IF EXISTS instead of testing the number of rows returned. IF EXISTS is more efficient, because it returns true as soon as it finds even a single row that matches the criteria.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 26, 2009 at 5:19 pm
Thank you so much Bob for the feedback. I have used If EXISTS before and I can see now (after your explaination that it would have been better.
I have never heard of the OUTPUT command but I will be looking it up. You mentioned books online? Can you provide a link to that site, as you can see I really need it.
Thanks again,
September 26, 2009 at 5:46 pm
Books Online is the documentation for SQL Server. You can get to it either by going to Help from SSMS, or:
Start | Programs | Microsoft SQL Server 2005 | Documentation and Tutorials | SQL Server Books Online
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 26, 2009 at 7:33 pm
You can also find it online through a web browser at:
http://msdn.microsoft.com/en-us/library/ms130214.aspx
Trudye, just to be clear. If you switch to using OUTPUT, I wouldn't bother with IF EXISTS at all. Just run the UPDATE, using OUTPUT to catch the before images of the rows. Everything gets done in one read through the main table.
Using IF EXISTS, you are doing a query to see if you need to do a query. The only situation where that saves you any time is if you find out that nothing needs to be updated. I presume that would be an unusual situation; and that as a general rule something will always get updated.
When there is nothing to be updated, you will have a row count of zero immediately following the UPDATE. You can use IF @@ROWCOUNT = 0 to condition any actions to be taken.
UPDATE SomeTable
SET SomeCoumn = SomeValue
WHERE SomeColumn < 16
IF @@ROWCOUNT = 0
BEGIN
-- code for actions to be taken on a "no updates" situation goes here
END
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 26, 2009 at 8:44 pm
September 26, 2009 at 8:54 pm
trudye10
Another potential gotcha .. if the column you are checking (Card_Num) is defined as CHAR or VARCHAR then the LEN function will do the job for you. If the column is defined as NVARCHAR as I have alterd Bob's sample codeto illustrate:
declare @sample table (string nvarchar(40))
insert into @sample
select N'Apples' union all
select N'Bananas' union all
select N'Coconuts' union all
select N'Paradimethylaminobenzaldehyde'
-- view the set of rows that have a length less than 20
-- this SELECT is NOT necessary to the UPDATE which follows
SELECT string,len(string) as strLength,DATALENGTH(String) as 'Bytes'
FROM @sample
Executing the above will then return:
string.......... strLength ..... Bytes
Apples ............... 6 ............. 12
Bananas............. 7 ............ 14
Coconuts ............ 8 ........... 16
Paradimethylaminobenzaldehyde29......... 58
From Books On Line:
Len is defined as:
the number of characters of the specified string expression, excluding trailing blanks.
To return the number of bytes used to represent an expression, use the DATALENGTH function
September 27, 2009 at 1:07 am
Really good point. Thanks for the heads up, BB.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 27, 2009 at 7:43 am
Thanks so much Bitbucket for the feedback. WHEW! I dodged that bullet, Card_Num is defined varchar. However I will keep that in mind for future development.
September 27, 2009 at 11:15 am
Thanks so much to all of you who stepped up and offered you time and talent in solving this problem.
Have a GREAT weekend.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply