Results 1 to 8 of 8

Thread: sql update

  1. #1
    Join Date
    Aug 2004
    Posts
    77

    Unanswered: sql update

    I have to update the date from 1 to 31. I have to update these information into 100 tables. Each table consistes of id, date and price
    id:: auto
    date:: date/time
    price:: number

    In other word I have to write these date into each table
    2005/03/01
    2005/03/01
    ...
    .
    .
    .
    .until
    2005/03/31
    How can I write sql statement. (I use Microsoft access and asp)

  2. #2
    Join Date
    Aug 2004
    Posts
    77
    I tired this..
    <!--#include file="connect.asp"-->
    <%
    Dim dte,i
    dte = "2005/03/01"
    For i = 1 To 32
    sql="UPDATE BA_price set dat = #" & dte & "# "
    conn.execute(sql)
    dte = DateAdd("d", 1, dte)
    Next
    %>
    But it still do not work. When I open database ,i got somthing like this
    id dat
    1 2005/01/04
    2 2005/01/04
    3 2005/01/04
    4 2005/01/04
    .
    .
    .
    .
    31 2005/01/04

    I want somthing like this
    id dat
    1 2005/03/01
    2 2005/03/02
    3 2005/03/03
    .
    .

    31 2005/03/31

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Your not telling it which row to update.

    sql="UPDATE BA_price set dat = #" & dte & "# "

    Will set every row in the table to the same value.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Aug 2004
    Posts
    77
    So now I write this
    <%
    Dim dte,i
    dte = "2005/03/01"
    For i = 1 To 31
    sql="UPDATE BA_price set dat = #" & dte & "# where id="&i&";"
    conn.execute(sql)
    dte = DateAdd("d", 1, dte)
    Next
    %>

    The result is this
    id dat
    1 2005/03/01
    2 2005/02/03
    3 2005/03/03
    4 2005/04/03
    5 2005/05/03
    6 2005/06/03
    7 2005/07/03
    8 2005/08/03
    9 2005/09/03
    10 2005/10/03
    11 2005/11/03
    12 2005/12/03
    13 2005/03/13
    14 2005/03/14
    15 2005/03/15
    16 2005/03/16
    .
    .
    31 2005/03/31

    Any suggestion

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I don't think that dte is being recognized as a date.
    Try changing to this
    sql="UPDATE BA_price set dat = #" & Cdate(dte) & "# where id="&i&";"
    and
    dte = DateAdd("d", 1, Cdate(dte))
    Inspiration Through Fermentation

  6. #6
    Join Date
    Aug 2004
    Posts
    77
    I still got the same result. !!

  7. #7
    Join Date
    Oct 2003
    Posts
    357
    gop373 try this logic
    Code:
    
    declare @t table(id int ,dat datetime)
    insert into @t(id) values(1)
    insert into @t(id) values(2)
    insert into @t(id) values(3)
    insert into @t(id) values(4)
    insert into @t(id) values(5)
    insert into @t(id) values(6)
    insert into @t(id) values(7)
    insert into @t(id) values(8)
    insert into @t(id) values(9)
    insert into @t(id) values(10)
    select * from @t
    update @t set dat ='2005/03/'+convert(varchar(2),id)
    select * from @t
    Madhivanan

    Failing to plan is Planning to fail

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving to microsoft access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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