Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: Update multiple records

    Hi,

    I want to update multiple records using SQL statement.
    Here is my table

    quareter (char(8))
    1992Q1
    1992Q2
    1992Q3
    1992Q4
    where Q is the quarter of year
    for this I used case statement and converted Q1 to 0331, Q2 to 0630, Q3 to 0930 and Q4 to 1231

    Select quarter
    CASE substring(quarter,5,6)
    when 'Q1'
    THEN substring(quarter,1,4) + '0331'
    when 'Q2'
    THEN substring(quarter,1,4) + '0630'
    when 'Q3'
    THEN substring(quarter,1,4) + '0930'
    when 'Q4'
    THEN substring(quarter,1,4) + '1231'
    End [quarter1]
    from emp
    This will display 1992031 for 1992Q1, 19920630 for 1992Q2 and etc.
    Now I want to update the column with the new values like 19920331 etc
    I am using update statement as
    update table set quarter where quarter in(
    Select quarter
    CASE substring(quarter,5,6)
    when 'Q1'
    THEN substring(quarter,1,4) + '0331'
    when 'Q2'
    THEN substring(quarter,1,4) + '0630'
    when 'Q3'
    THEN substring(quarter,1,4) + '0930'
    when 'Q4'
    THEN substring(quarter,1,4) + '1231'
    End [quarter1]
    from emp)

    I am not sure this will work, can anyone confirm this. If this doesn't work please provide me with the correct SQL statement.
    Any help is appreciated
    Thanks
    Srinivas chityala

  2. #2
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Thumbs up

    Hi !!!
    Try this:

    USE tempdb
    go

    CREATE TABLE xxx (
    quarter char(8) null
    )
    go

    INSERT INTO xxx VALUES ( "1992Q1" )
    go
    INSERT INTO xxx VALUES ( "1992Q2" )
    go
    INSERT INTO xxx VALUES ( "1992Q3" )
    go
    INSERT INTO xxx VALUES ( "1992Q4" )
    go

    SELECT * FROM xxx
    go

    quarter
    -------
    1992Q1
    1992Q2
    1992Q3
    1992Q4


    -- be carefull with the log spaces !!!! if the table is too big
    UPDATE xxx
    SET quarter = CASE substring(quarter,5,6) WHEN 'Q1'
    THEN substring(quarter,1,4) + '0331'
    WHEN 'Q2'
    THEN substring(quarter,1,4) + '0630'
    WHEN 'Q3'
    THEN substring(quarter,1,4) + '0930'
    WHEN 'Q4'
    THEN substring(quarter,1,4) + '1231'
    END
    go

    SELECT * FROM xxx
    go

    quarter
    -------
    19920331
    19920630
    19920930
    19921231

    DROP TABLE xxx
    go

    Bye

    Sebastian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •