Making Changes by Cursor Permanent in a Table

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

SQL Server 2000 Forums

Transact-SQL (2000)

Making Changes by Cursor Permanent in a Table

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-26 : 18:10:56

Got a head-scratcher here. I'm looking to fix the dates in the BigDateFile (25K records) to ensure they are formatted correctly (leading zeroes in months and days). The problem is getting the changes I made to stick. Because when I look at BigDateFile, the changes aren't there, even though I did PRINTs to see that they were done.

I'm certain there's a minor thing I've missed. Anyone take a shot at this? Thanks!

Here's the code:

DECLARE
@RowNum int,
@STARTDATE varchar(10),
@MO varchar(2),
@DA varchar(2),
@YR varchar(4)

-- declare the cursor
DECLARE DATECursor CURSOR FOR

SELECT STARTDATE
FROM BigDateFile

OPEN DATECursor

FETCH NEXT FROM DATECursor
INTO @STARTDATE

SET @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RowNum = @RowNum + 1

PRINT '...input....'+@STARTDATE
SET @MO = CAST(DATEPART(M,@STARTDATE) as varchar(2))
SET @MO = RIGHT('0' + @MO,2)

SET @DA = CAST(DATEPART(D,@STARTDATE) as varchar(2))
SET @DA = RIGHT('0' + @DA,2)

SET @YR = RIGHT(@STARTDATE,4)

PRINT '...target...'+@MO+'/'+@DA+'/'+@YR

SET @STARTDATE = @MO+'/'+@DA+'/'+@YR
PRINT '...result...'+CAST(@RowNum as char(5))+' '+@STARTDATE+' '+@MO+' '+@DA+' '+@YR

FETCH NEXT FROM DATECursor INTO @STARTDATE
END

CLOSE DATECursor
DEALLOCATE DATECursor
GO

So what am I missing here?

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-26 : 18:19:28

>>So what am I missing here?

quite a few things!

1) you are using a cursor when the entire thing could be 1 single UPDATE statement

2) you aren't updating anything in your code other than a variable, so i'm not sure what should be changing

3) why are you doing this? why aren't you using DateTime datatypes?

4) don't confuse storage of data in your tables with formatting/presentation

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-26 : 18:25:20

Pardon me, Dr. Cross Join, but I seem to recall that I can only perform ONE SET statement per UPDATE. And I need to check BOTH the months & days figures in the dates.

If I could have used the UPDATE I would have.

Semper fi

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-26 : 18:43:41

No need to get snarky about it. Jeff is correct.
You can set multiple values in an UPDATE statement, you just can't SET multiple variables in one go.

You aren't updating the table, you're just setting variables.

Damian

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 09:27:59

quote:
Originally posted by Merkin

No need to get snarky about it. Jeff is correct.
You can set multiple values in an UPDATE statement, you just can't SET multiple variables in one go.

You aren't updating the table, you're just setting variables.

Damian

Damian,

Would you please clarify this? Thanks.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-27 : 09:40:58

it might be helpful to answer some of the questions I posted, and to explain to us what you are trying to do with some sample data. Again, did you see that you are just updating a VARIABLE and not actually updating any table rows at all in the code you posted? Which line in your code, in your opinion, should be updating the table? Why are you using PRINT statements to attempt verify the data in your tables? Are you familiar with the things you can do with SELECT statements?

You might want to check out some good books on SQL, and practice with Northwind a little to experiment and get a feel for things. Try updating sets of rows, returning rows, looking at the different data types, and make sure you have a good understand of how data is STORED versus how it can be DISPLAYED at any given time. YOu should almost never be trying to update rows in a database to change the way the data is displayed -- that is a job for the presentation layer to take raw data from the database and display it however you need at that given time.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 09:53:55

Thank you.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 10:18:33

This is CLOSE to what I was trying to achieve...

SELECT *,
CAST(DATEPART(M,STARTDATE) as varchar(2)) as MO,
CAST(DATEPART(D,STARTDATE) as varchar(2)) as DA,
RIGHT(STARTDATE,4) as YR
FROM BigDateFile
GO
UPDATE BigDateFile
SET STARTDATE = RIGHT('0'+MO,2)+'/'+RIGHT('0'+DA,2)+'/'+YR
GO

....although the update isn't taking...

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 10:28:29

This is the solution I settled on, but it's rather CLUNKY. Anyone have anything that might be smoother?

UPDATE BigDateFile
SET STARTDATE =
RIGHT('0'+ (CAST(DATEPART(M,STARTDATE) as varchar(2))),2)+'/'+
RIGHT('0'+ (CAST(DATEPART(D,STARTDATE) as varchar(2))),2)+'/'+
RIGHT(STARTDATE,4)
GO

Thanks....

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-27 : 11:11:39

Why are you doing this? what datatype is STARTDATE ?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-27 : 11:28:18

Better yet...Just tell us, in business terms, what you are trying to do.

Because it looks like you're trying to update the same field with the same data.

Also, for use to help..it's best to post the DDL of the table(s), some sample Data in DML format and the expected results after the operation.

Interesting

http://plato-dialogues.org/tools/char/xerxes.htm

Brett

8-)

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 11:31:27

The STARTDATE datatype is varchar(10). I set it up that way because this file was DTS-ed from dBase 5 and I need the date format to be uniform so that I may make comparisons to other tables.

I just needed to get the date formatting resolved. I've been doing SQL solely for 6 months converting an entire dBase 5 system to SQL. Although I used SQL ( when programming in SAS against DB2) a great deal in the Corps and post '95.

Like most who started in IT back in the mid-70's, I'm used to the IF-THEN-ELSE style of programming that comes with SAS, Natural/ADABAS, dBase, FoxPro, and Clipper. And unless you're on Oracle, you can't use the IF-THEN-ELSE cheater tool: DECODE. So I'm saddled with pages of single 'UPDATE(table)/SET(field)' statements to mimic edit loops.

All I was looking for was a way out of the single UPDATE and (one) SET. If I could perform multiple edits (aside from the SELECT 'xxx' as NewField) like I can in SAS or ADABAS loops, it would save me a lot of grief.

Thanks for your assistance.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-27 : 11:31:52

quote:
Originally posted by Xerxes

This is the solution I settled on, but it's rather CLUNKY. Anyone have anything that might be smoother?

UPDATE BigDateFile
SET STARTDATE =
RIGHT('0'+ (CAST(DATEPART(M,STARTDATE) as varchar(2))),2)+'/'+
RIGHT('0'+ (CAST(DATEPART(D,STARTDATE) as varchar(2))),2)+'/'+
RIGHT(STARTDATE,4)
GO

Thanks....

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

By the way -- if that's "clunky", what word would you use to describe your original 25 lines of cursor code?

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 11:38:34

Jeff,

I would describe my previous code to be CLUNKIER.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-27 : 11:51:02

Well it doesn't work...



USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(STARTDATE varchar(10))
GO

INSERT INTO myTable99(STARTDATE)
SELECT '12312004' UNION ALL
SELECT '12/31/2004' UNION ALL
SELECT '1312004' UNION ALL
SELECT '112004'
GO

SELECT    RIGHT('0'+ (CAST(DATEPART(M,STARTDATE) as varchar(2))),2)+'/'
	+ RIGHT('0'+ (CAST(DATEPART(D,STARTDATE) as varchar(2))),2)+'/'
	+ RIGHT(STARTDATE,4)
  FROM myTable99 
GO

Could you post some code like this so we can see what your doing?

And let me get this straight...you've been in IT since the 70's and have just started doing SQL?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-27 : 11:57:39

i don't understand what you mean by "you can only do single updates". you know that a single UPDATE statment can update all rows (or selections of rows) in a table all at once, right? and that it can update multiple columns all at once, too?

update YourTable
set col1 = <any expression>, col2 = <any expression>, col3= <any expression>, ..etc...
where <some condition>

and in the above, <any expression> can be just about anything -- even another SELECT statement from a related table if needed.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-27 : 12:01:08

>>The STARTDATE datatype is varchar(10). I set it up that way because this file was DTS-ed from dBase 5 and I need the date format to be uniform so that I may make comparisons to other tables.

That is no reason to make STARTDATE a varchar. if this data is all in sql, use the correct datatypes, and then formatting is not an issue. Using the correct datatypes will solve you lots of heachaches, lets you select on ranges properly (which you cannot do with your "formatted" columns), and so on. Again, do not confuse how you are storing data with how you may need to display it or output it later on. Does this make sense? You keep ignoring this point of view, and it's a very important one to understand when working with databases.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 12:16:41

Jeff,

I can see now that I could have set up the format during DTS from dBase 5.

In regard to the structure:

update YourTable
set col1 = <any expression>, col2 = <any expression>, col3= <any expression>, ..etc...
where <some condition>

I was referring to the fact that you can use only ONE SET statement per UPDATE. I cannot do this, for example:

UPDATE ReallyHugeFile
SET FIELD001 = <some expression> <<===can ONLY do this: 1 SET per UPDATE
SET FIELD057 = <some other expression> <<===can't do this (although would be nice)
SET FIELD811 = <other expression> <<===can't do this either

Again, I am not confusing data storage with need to display. The PRINT statement in the first thread iteration were so I could see in SQL Analyzer whether the changes were occurring.

Thank you so much for your assistance. I'm going to ensure my datatypes are correct so I can avoid reformatting.

Thanks.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-27 : 13:17:51

YES YOU CAN! i just showed that to you ! did you try it? What made you think you can only update 1 column at a time? You just need to use the correct syntax (which I showed you in my example).

again:

update SomeTable
set Column1 = @Value1, Column2 = @Value2, Column3=@value3, ... etc...

Does that make any sense to you whatsoever? Don't make assumptions about what you can and cannot do just because what you have tried gives a syntax error.

Read more about it in Books On-Line. No offense, but before going too much further on this or any other projects, i HIGHLY recommend a good book on SQL.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 13:31:50

Omigosh! I can't believe I didn't see that!

Arrrgh! NOW I understand it! And it makes perfect sense! Simply put commas between the statements--not a 'SET' before each one.

Gee, sometimes I can't see the forest for the trees!

Guess I'm thinking that the command has to preceed each statement when a simple comma would do.

Thanks for clarifying this Jeff. It makes total sense!

BTW, I have these: "SQL Fundamental" John J. Patrick and "SQL in a Nutshell" Kevin Kline.

Thanks again for your help.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 14:00:23

quote:
Originally posted by X002548

Interesting

http://plato-dialogues.org/tools/char/xerxes.htm

Brett

8-)

Brett, did you get my e-mail?

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources