locked
update statement problem RRS feed

  • Question

  • Hello,

           I am in urgent help right now, i have one table with WeekEndDatekey which is always be a saturday and another column VALUE which i need to update! i need to update this value from other table which has two date columns and one value column, it is something like this

     Fact table

    WeekendDatekey     Value

      20111105                0

      20111112                0

      20111119                0

     

    Another table

    BeginDate          EndDate            Value

    20111002          20111105          5

    20111106          20111119          12

     

    So when i update result must be like this,

    Fact table

    20111105             5

    20111112             12

    20111119             12

     


    ANK HIT
    Wednesday, November 9, 2011 3:16 PM

Answers

  •  

    update Fact
    
    set Value = AT.Value
    
    from Fact inner join AnotherTable AT on Fact.WeekendDatekey between AT.BeginDate and At.EndDate
    

     

    This uses proprietary syntax.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Hasham NiazEditor Wednesday, November 9, 2011 4:14 PM
    • Marked as answer by ank hit Wednesday, November 9, 2011 4:38 PM
    Wednesday, November 9, 2011 3:28 PM
  • No wonder. Do you want to discard the year portion then? If so, instead of using dates directly, use right(convert(varchar(10),Dates,112),4) in comparison to compare only month and day but not year. The performance of such query may suffer.

    Actually, such query may not work correctly. You will need to convert both dates back into dates but using the same year.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Wednesday, November 9, 2011 4:09 PM
    • Marked as answer by ank hit Wednesday, November 9, 2011 4:37 PM
    Wednesday, November 9, 2011 4:07 PM
  • Thanks a lot ma'am, it really worked but i modified your quary a bit.

    I remembered one such scenario i faced few months back, to convert dates, i used the same conversion over here like,

    your query is cent percent correct but lets say we have a date like 2011-05-25 with value 5 and 2010-05-25 with 12, it will grab wrong data, since its not like whatever data is coming right now will not change in future! because it is not coming from any file or any table but from infopath form which i have created for end user to insert and modify data!

     

    UPDATE #3

     

    SET VALUE= Z.VALUE

     

    FROM #3 AS A INNER JOIN #1 AS Z

     

    ON (Cast(Replace(Convert(varchar(10), A.DATES, 120), '-', '') As int)) BETWEEN
     

     

    (Cast(Replace(Convert(varchar(10), Z.BEGINDATE, 120), '-', '') As int)) AND

     

    (Cast(Replace(Convert(varchar(10), Z.ENDDATE, 120), '-', '') As int))

     

     

     


    ANK HIT
    • Marked as answer by ank hit Wednesday, November 9, 2011 4:37 PM
    Wednesday, November 9, 2011 4:37 PM
  • Hi ank !

    You might need below query;

    CREATE TABLE FactTable (WeekEndDateKey DATE,VALUE INT)
    CREATE TABLE AnotherTable (BeginDate DATE,EndDate DATE,VALUE INT)
    --TRUNCATE TABLE FactTable
    --TRUNCATE TABLE AnotherTable
    INSERT INTO FactTable
    SELECT '20101106',0 UNION ALL
    SELECT '20101113',0 UNION ALL
    SELECT '20101120',0 UNION ALL
    SELECT '20101127',0 UNION ALL
    SELECT '20101130',0 
    
    INSERT INTO AnotherTable
    SELECT '20111106','20111126',1 UNION ALL
    SELECT '20111002','20111105',2
    
    --SELECT *
    UPDATE F SET F.VALUE = A.VALUE
    FROM FactTable F
    INNER JOIN AnotherTable A ON SUBSTRING(CONVERT(VARCHAR,F.WeekEndDateKey,112),5,4) >= SUBSTRING(CONVERT(VARCHAR,A.BeginDate,112),5,4)
           AND SUBSTRING(CONVERT(VARCHAR,F.WeekEndDateKey,112),5,4) <= SUBSTRING(CONVERT(VARCHAR,A.EndDate,112),5,4)
    
    SELECT * FROM FactTable
    --WeekEndDateKey Value
    --2010-11-06  1
    --2010-11-13  1
    --2010-11-20  1
    --2010-11-27  0
    --2010-11-30  0
    
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    • Marked as answer by ank hit Wednesday, November 9, 2011 4:48 PM
    Wednesday, November 9, 2011 4:38 PM
    Answerer

All replies

  •  

    update Fact
    
    set Value = AT.Value
    
    from Fact inner join AnotherTable AT on Fact.WeekendDatekey between AT.BeginDate and At.EndDate
    

     

    This uses proprietary syntax.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Hasham NiazEditor Wednesday, November 9, 2011 4:14 PM
    • Marked as answer by ank hit Wednesday, November 9, 2011 4:38 PM
    Wednesday, November 9, 2011 3:28 PM
  •  

    UPDATE #3

     

    SET VALUE= Z.VALUE

     

    FROM #3 AS A INNER JOIN #1 AS Z

     

    ON A.DATES between Z.BEGINDATE and Z.ENDDATE

    --- it says 0 rows affected


    ANK HIT
    Wednesday, November 9, 2011 3:40 PM
  • Change the update into select first to see, what may be updated, e.g.

     

    select a.*, b.*
    
    from [#3] as inner join [#1] b on a.Dates between b.BeginDate and b.EndDate
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, November 9, 2011 3:48 PM
  • table #3 values:

    DATES                                          VALUE
    2010-11-06 00:00:00.000              0
    2010-11-13 00:00:00.000              0
    2010-11-20 00:00:00.000              0
    2010-11-27 00:00:00.000              0
    2010-11-30 00:00:00.000              0

    table #1 values:

               BEGINDATE                                       ENDDATE                    VALUE
    2011-11-06 00:00:00.000          2011-11-26 00:00:00.000              1
    2011-10-02 00:00:00.000          2011-11-05 00:00:00.000              2

    so above mentioned query gives me 0 rows.


    ANK HIT
    Wednesday, November 9, 2011 4:01 PM
  • No wonder. Do you want to discard the year portion then? If so, instead of using dates directly, use right(convert(varchar(10),Dates,112),4) in comparison to compare only month and day but not year. The performance of such query may suffer.

    Actually, such query may not work correctly. You will need to convert both dates back into dates but using the same year.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Wednesday, November 9, 2011 4:09 PM
    • Marked as answer by ank hit Wednesday, November 9, 2011 4:37 PM
    Wednesday, November 9, 2011 4:07 PM
  • Thanks a lot ma'am, it really worked but i modified your quary a bit.

    I remembered one such scenario i faced few months back, to convert dates, i used the same conversion over here like,

    your query is cent percent correct but lets say we have a date like 2011-05-25 with value 5 and 2010-05-25 with 12, it will grab wrong data, since its not like whatever data is coming right now will not change in future! because it is not coming from any file or any table but from infopath form which i have created for end user to insert and modify data!

     

    UPDATE #3

     

    SET VALUE= Z.VALUE

     

    FROM #3 AS A INNER JOIN #1 AS Z

     

    ON (Cast(Replace(Convert(varchar(10), A.DATES, 120), '-', '') As int)) BETWEEN
     

     

    (Cast(Replace(Convert(varchar(10), Z.BEGINDATE, 120), '-', '') As int)) AND

     

    (Cast(Replace(Convert(varchar(10), Z.ENDDATE, 120), '-', '') As int))

     

     

     


    ANK HIT
    • Marked as answer by ank hit Wednesday, November 9, 2011 4:37 PM
    Wednesday, November 9, 2011 4:37 PM
  • Hi ank !

    You might need below query;

    CREATE TABLE FactTable (WeekEndDateKey DATE,VALUE INT)
    CREATE TABLE AnotherTable (BeginDate DATE,EndDate DATE,VALUE INT)
    --TRUNCATE TABLE FactTable
    --TRUNCATE TABLE AnotherTable
    INSERT INTO FactTable
    SELECT '20101106',0 UNION ALL
    SELECT '20101113',0 UNION ALL
    SELECT '20101120',0 UNION ALL
    SELECT '20101127',0 UNION ALL
    SELECT '20101130',0 
    
    INSERT INTO AnotherTable
    SELECT '20111106','20111126',1 UNION ALL
    SELECT '20111002','20111105',2
    
    --SELECT *
    UPDATE F SET F.VALUE = A.VALUE
    FROM FactTable F
    INNER JOIN AnotherTable A ON SUBSTRING(CONVERT(VARCHAR,F.WeekEndDateKey,112),5,4) >= SUBSTRING(CONVERT(VARCHAR,A.BeginDate,112),5,4)
           AND SUBSTRING(CONVERT(VARCHAR,F.WeekEndDateKey,112),5,4) <= SUBSTRING(CONVERT(VARCHAR,A.EndDate,112),5,4)
    
    SELECT * FROM FactTable
    --WeekEndDateKey Value
    --2010-11-06  1
    --2010-11-13  1
    --2010-11-20  1
    --2010-11-27  0
    --2010-11-30  0
    
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks,
    Hasham

    • Marked as answer by ank hit Wednesday, November 9, 2011 4:48 PM
    Wednesday, November 9, 2011 4:38 PM
    Answerer