Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: Update table 1 to table 2

    hi...im trying to update one of my tables by using update...im using asp for my scripting lang. and access for my db...table1 has the following:tempusers_tbl --> id, name, comments...table2:users_tbl --> id, name, comments...here is the code i have...
    <%
    'declare your variables
    Dim name, email, comments
    Dim sConnString, connection, sSQL

    sSQL = "update users_tbl
    set name = (select name
    from tempusers_tbl)"

    'define the connection string, specify database
    'driver and the location of database
    sConnString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
    "DBQ=" & Server.MapPath("users.mdb") & ";"

    'create an ADO connection object
    Set connection = Server.CreateObject("ADODB.Connection")

    'Open the connection to the database
    connection.Open(sConnString)

    'execute the SQL
    connection.execute(sSQL)

    ' Done. Close the connection object
    connection.Close
    Set connection = Nothing

    %>

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Your query is invalid.

    Update tableA set columnA = (select a from b) will only work if the sub-query returns exactly one row.

    It appears that you want to insert all user details from tableA into tableB.

    Insert into tableA select name from (select name from tableB tb where NOT EXISTS (select null from tableA where name = tb.name))

    The NOT EXISTS condition is only required if your TEMP table is not updated once a record has been transferred into tableA.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jun 2004
    Posts
    9

    Question

    thanks for your reply...that still doesn't work...is there different syntax in the sql statement if im coding it in asp?...this is what i have...

    dim sSQL
    sSQL = "Insert into users_tbl select name from (select name from tempusers_tbl tb where NOT EXISTS & _
    (select null from users_tbl where name = tb.name))"

    thanks again for your help...

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The SQL is correct for Oracle 9i & SQL Server 2000

    SQL = "Insert into users_tbl select name from (select name from tempusers_tbl tb where NOT EXISTS " & _
    "(select null from users_tbl where name = tb.name))"
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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