Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    88

    Unanswered: How to update time series based on another table(or view)??

    Hi all,
    I have two tables (staging and Cdate) and neither objects has any constraints.
    staging table has ID, date, A, B, and C fields and Cdate has id,date and day fields. I need to update/insert date from Vdate into staging where staging ID=' ' and date is null
    Here is the code I wrote, however, it seemed the information was updated to one date only instead of time series - Cdate contains time series in column date.
    Anyone can help to fix it? Thank you for the help!

    update s
    set s.date=c.date
    FROM cdate c join staging s on(s.id=c.id)
    Where s.date is null and id=2

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Provide DDL, sample data and expected results. The simpler you make it for us, especially eliminating any guesses we have to make, the better we can help you.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  3. #3
    Join Date
    Feb 2005
    Posts
    88
    Make sense.
    Here is the staging table look like:
    id Date A B C
    1 1/1/91 X X X
    1 1/2/91 X X X
    2 Null Y Y Y
    2 Null Y Y Y
    2 1/1/91 Y Y Y
    2 1/2/91 Y Y Y
    ...
    Cdate Table:
    id Date day
    2 12/30/90 Mon
    2 12/31/90 Tue
    ...

    I would like to update [date] column where Id=2 and [date] is null by using [date] info from Cdate table.

  4. #4
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    You left out the DDL, however you are breaking an important rule - you do not have a primary key on the staging table, thus you have duplicate rows and the Update that follows is nondeterministic (your results will vary).

    -- I would like to update [date] column where Id=2 and [date] is null by using [date] info from Cdate table.
    Code:
    Declare @staging Table(id int, TheDate datetime, A varchar(1) Null, B varchar(1) Null, C varchar(1) Null)
    Declare @cdate Table (id int, TheDate datetime, DOW varchar(3))	-- DOW is redundant
    
    Insert @staging
    Select 1, '1/1/91', 'X', 'X', 'X'
    Union All
    Select 1, '1/2/91', 'X', 'X', 'X'
    Union All
    Select 2, Null, 'Y', 'Y', 'Y'
    Union All
    Select 2, Null, 'Y', 'Y', 'Y'
    Union All
    Select 1, '1/1/91', 'X', 'X', 'X'
    Union All
    Select 1, '1/2/91', 'X', 'X', 'X'
    
    Insert @cdate
    Select 2, '12/30/90', 'Mon'
    Union
    Select 2, '12/31/90', 'Tue'
    
    Begin Tran
    	Select s.*, '|' As 'Bar', c.*
    	From @staging s
    		Join @cdate c On c.id = s.id
    	Where s.id = 2 And s.TheDate Is Null
    
    	Update s
    	Set TheDate = c.TheDate
    	From @staging s
    		Join @cdate c On c.id = s.id
    	Where s.id = 2 And s.TheDate Is Null
    	
    	Select s.*, '|' As 'Bar', c.*
    	From @staging s
    		Join @cdate c On c.id = s.id
    	Where s.id = 2 And s.TheDate Is Not Null
    Rollback Tran
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

Posting Permissions

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