none
Update with the join of date function

    Question

  • Hi, I want to update t_code of #table2 from #table1 and want to join t_date but with yyyy-dd-mm hh:mm and should ignore seconds in t_date column. Please help on this update. Thanks.

    create table #table1 (T_code int, t_date datetime)
    insert into #table1 values (300,'2012-01-15 05:43:26.821')
    insert into #table1 values (301,'2012-01-15 06:45:26.821')
    insert into #table1 values (302,'2012-01-15 06:45:27.821')
    insert into #table1 values (303,'2012-01-15 06:45:28.821')

    create table #table2 (c_code int,T_code int, t_date datetime)

    insert into #table2 values (400,120,'2012-01-15 05:43:27.822')
    insert into #table2 values (401,121,'2012-01-15 06:45:28.824')
    insert into #table2 values (402,122,'2012-01-15 06:45:29.825')
    insert into #table2 values (403,123,'2012-01-15 06:45:30.836')

    /*update a
    set a.T_code=b.T_code
    from #table2 a
    join  #table1 b
    on a.t_date=b.t_date*/

    --Expected output

    Select * from #table2
    c_code T_code t_date
    400  300  2012-01-15 05:43:27.822
    401  301  2012-01-15 06:45:28.824
    402  302  2012-01-15 06:45:29.825
    403  303  2012-01-15 06:45:30.836




    • Edited by Kenny_Gua Saturday, August 02, 2014 2:26 AM
    Saturday, August 02, 2014 12:26 AM

Answers

  • Yes you will not get the result for just removing the seconds.

    You can just add an ID on both tables.

    Maybe you can move the data in another temp table with ID column that has an identity.

    create table #table1 (T_code int, t_date datetime)
    insert into #table1 values (300,'2012-01-15 05:43:26.821')
    insert into #table1 values (301,'2012-01-15 06:45:26.821')
    insert into #table1 values (302,'2012-01-15 06:45:27.821')
    insert into #table1 values (303,'2012-01-15 06:45:28.821')
    create table #table2 (c_code int,T_code int, t_date datetime)
    
    insert into #table2 values (400,120,'2012-01-15 05:43:27.822')
    insert into #table2 values (401,121,'2012-01-15 06:45:28.824')
    insert into #table2 values (402,122,'2012-01-15 06:45:29.825')
    insert into #table2 values (403,123,'2012-01-15 06:45:30.836')
    
    --Create another temp table with ID
    create table #tablea (ID int identity(1,1),T_code int, t_date datetime)
    create table #tableb (ID int identity(1,1),c_code int,T_code int, t_date datetime) 
    --insert table value from table1 and table2
    insert into #tablea(T_code,t_date) select * from #table1 order by t_date
    insert into #tableb(c_code,T_code,t_date) select * from #table2  order by t_date
    
    select * from #tablea
    select * from #tableb
    --update the temp tableb
    update a
    set a.T_code=b.T_code
    from #tableb a
    join  #tablea b
    on a.ID=b.ID
    --delete records in table2
    truncate table #table2
    --insert records from tableb to table2
    insert into #table2 select c_code,T_code,t_date from #tableb
    
    select * from #table1
    select * from #table2

    • Edited by JamesEarnan Saturday, August 02, 2014 3:28 AM
    • Marked as answer by Kenny_Gua Saturday, August 02, 2014 5:29 AM
    Saturday, August 02, 2014 3:15 AM

All replies

  • Hi, Can anyone please answer. Thanks.
    Saturday, August 02, 2014 2:27 AM
  • You cannot get the result with your sample data. You dates without second will have a many to many relation and it will not update correctly.
    Saturday, August 02, 2014 2:31 AM
    Moderator
  • Hi, Can we replace the last numbers in dates with (00.000) i.e. yyyy-mm-dd mm:ss:00.000 and then join T_date of #table2 with T_date of #table1 for joining purpose to update the "T_CODE of #table2". Thanks.

    --Expected output

    Select * from #table2
    c_code T_code t_date
    400  300  2012-01-15 05:43:27.822
    401  301  2012-01-15 06:45:28.824
    402  302  2012-01-15 06:45:29.825
    403  303  2012-01-15 06:45:30.836

    Saturday, August 02, 2014 2:54 AM
  • Yes you will not get the result for just removing the seconds.

    You can just add an ID on both tables.

    Maybe you can move the data in another temp table with ID column that has an identity.

    create table #table1 (T_code int, t_date datetime)
    insert into #table1 values (300,'2012-01-15 05:43:26.821')
    insert into #table1 values (301,'2012-01-15 06:45:26.821')
    insert into #table1 values (302,'2012-01-15 06:45:27.821')
    insert into #table1 values (303,'2012-01-15 06:45:28.821')
    create table #table2 (c_code int,T_code int, t_date datetime)
    
    insert into #table2 values (400,120,'2012-01-15 05:43:27.822')
    insert into #table2 values (401,121,'2012-01-15 06:45:28.824')
    insert into #table2 values (402,122,'2012-01-15 06:45:29.825')
    insert into #table2 values (403,123,'2012-01-15 06:45:30.836')
    
    --Create another temp table with ID
    create table #tablea (ID int identity(1,1),T_code int, t_date datetime)
    create table #tableb (ID int identity(1,1),c_code int,T_code int, t_date datetime) 
    --insert table value from table1 and table2
    insert into #tablea(T_code,t_date) select * from #table1 order by t_date
    insert into #tableb(c_code,T_code,t_date) select * from #table2  order by t_date
    
    select * from #tablea
    select * from #tableb
    --update the temp tableb
    update a
    set a.T_code=b.T_code
    from #tableb a
    join  #tablea b
    on a.ID=b.ID
    --delete records in table2
    truncate table #table2
    --insert records from tableb to table2
    insert into #table2 select c_code,T_code,t_date from #tableb
    
    select * from #table1
    select * from #table2

    • Edited by JamesEarnan Saturday, August 02, 2014 3:28 AM
    • Marked as answer by Kenny_Gua Saturday, August 02, 2014 5:29 AM
    Saturday, August 02, 2014 3:15 AM
  • Thank you so much for your help.
    Saturday, August 02, 2014 5:29 AM
  • update a
    set a.T_code=b.T_code
    from #table2 a
    join  #table1 b
    on Convert(smalldatetime,a.t_date)= Convert(smalldatetime,b.t_date)

    Hope this solves your problem. Please do update me if it did. This might add on a little to your overhead because of the convert functions.


    Regards Keerthi Kiran Hope this answered your question


    Saturday, August 02, 2014 6:31 AM