Post reply

How do I copy the value of one column into another?

  • June 9, 2005 at 9:26 am

    #164013

    Newbie Alert!

    I'm creating a Stored Procedure within Query Analyser and would like to know how to copy the contents of one column into another.

    The column names are Day1, Day2 ............. Day30, Day31 and I want to copy the contents of Day2 to Day1, Day3 to Day2 etc, etc.

    I've tried Set Day1 = Day2 but I keep getting this error message

    Line 24: Incorrect syntax near '='.

    If you can help, please supply a code example that I can work from.

    Thank you

  • June 9, 2005 at 9:43 am

    #564281

    I ran this and it works fine.

    create table Temp(pk int identity, day1 datetime, day2 datetime, day3 datetime, day4 datetime)

    insert into temp (day1,day2,day3,day4)

    values ('01/01/2005', '01/2/2005', '01/3/2005', '01/4/2005')

    update Temp

    set day1 = day2,

        day2 = day3,

        day3 = day4

    select * from temp

    try posting a ddl, and update code so the error can be pointed out.

  • June 9, 2005 at 10:13 am

    #564291

    What are you trying to achieve exactly??

    This doesn't seem like a normalized system to me!!!

  • June 9, 2005 at 12:15 pm

    #564338

    Thanks Ray, that's what I was after.

  • June 9, 2005 at 12:42 pm

    #564346

    I strongly suggest that you don't ignore my questions... It's gonna come byte you in the ass down the line.

  • June 9, 2005 at 6:50 pm

    #564453

    Why's that then? I've got the table working exactly how I want it.

    A rolling 30 day record of activity gets registered; the oldest one gets replaced with the second oldest one and so on; and a new day is set up in the Day30 column via a scheduled job.

    It's now a slick and practical routine. What's the alternative??

  • June 9, 2005 at 8:51 pm

    #564465

    Create table Activities

    (

    ActivityDate datetime (primary key clustered)

    ActivityDescription varchar(100)

    )

    Then you can run this proc daily :

    Delete from dbo.Activities where ActivityDate < DateAdd(M, -1, GetDate())

    Is this simpler than your code?

  • June 9, 2005 at 8:56 pm

    #564467

    I forgot to mention that you might want to truncate the time of the day for GetDate().

    It would look something like this :

    DATEADD(d,0,DATEDIFF(d,0, GetDate()))

  • June 10, 2005 at 4:07 am

    #564527

    Thanks Remi, you're right, it's another slick piece of code and another one which I'll keep in my useful code folder. I've got something coming up in the next couple of days where I'll certainly be able to use this method.

    Thanks for your help.

  • June 10, 2005 at 6:36 am

    #564551

    HTH.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply