Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    13

    Question Unanswered: How to insert a value based on lookup from another table [SQL]?

    I need to find a way to do an INSERT INTO table A but one of the values is something that comes from a lookup on table B, allow me to illustrate.

    I have the 2 following tables:

    Table A:
    A1: String
    A2: Integer value coming from table B
    A3: More Data

    Table B:
    B1: String
    B2: Integer Value


    Example row of A: {"Value", 101, MoreData}
    Example row of B: {"English", 101}

    Now, I know I need to INSERT the following into A {"Value2", "English", MoreData} but obviously that won't work because it is expecting an Integer in the second column not the word "English", so I need to do a lookup in Table B first.

    Something like this:
    INSERT INTO tableA (A1, A2, A3) VALUES ("Value2", SELECT B2 FROM tableB where B1="English", MoreData);

    Obviously this doesn't work as-is ...
    Any suggestions?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In an VALUES clause, you can only specify variables, or constants.

    Figure you could write a SELECT statement that returns the data you want to have inserted into the table?

  3. #3
    Join Date
    Jan 2006
    Posts
    13
    There has to be a way to do this...
    And I am not sure what you mean by your comment ...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    INSERT INTO tableA (A1, A2, A3)
    SELECT 'Value2', B2, 'MoreData' FROM tableB where B1='English'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There is. Just nudging you towards learning the answer.

    If instead of "insert these rows" you were asked to "select these rows", what would you write?

Posting Permissions

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