Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Unanswered: access 2000 sql insert command

    I have three tables t1, t2, t3. I want to insert a new row in t1. I am using access 2000 with sql commands for the queries. There are 4 colums in t1. I know the values of two of the colums. The remaining two columns values are going to be look up in the other 2 tables, 1 value from t2 table and the other value from t3.. I need this to be in one statement. From what I have seen you can either use insert with values() or you can populate your table with unkown values from another table. But I want to do both, since 2 of my values are known and the other 2 need to come from another table.

    insert into t1 (col1, col2, col3, col4)


    col1 I know and I am going to hardcode it ex. 'free'
    col2 I know and I am going to hardcode it ex. 'loader'
    col3 will be from t2 // where t2.id = t3.id, t3.name = 'free'
    col4 will be from t3 // where t3.job = t2.job, t2.last = 'loader'

    something like that

    I hope that I made it clear enough. Can anyone help me

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    two-table situation is easy enough if you have the SomeValue for the WHERE

    INSERT INTO t1
    SELECT
    "free" AS col1,
    "loader" AS col2,
    t2.SomeField AS col3,
    t2.OtherField AS col4
    FROM t2
    WHERE t2.id = SomeValue;

    extend to three tables by SELECTing from an appropriate JOIN between t2 and t3

    izy
    currently using SS 2008R2

Posting Permissions

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