Results 1 to 4 of 4

Thread: Get Value from Table Prior to Insert

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,698

    Get Value from Table Prior to Insert

    I am using MySQL and have a table with the following fields:
    • user_id
    • business_id
    • access_id
    • email
    • password
    • first_name
    • last_name
    • title
    • signature
    • is_active


    I also have another table with the following fields:
    • business_id
    • request_id
    • subject
    • send_date
    • body
    • customer_type_id


    I need to insert a row in the second table, but the information that I have is the request_id, subject, send_date, body, customer_type_id, and user_id (references table1). How can I get the business_id from the first table using the user_id value prior the insert command without having to run two separate queries.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Get Value from Table Prior to Insert

    You can use a Select statement (with explicit values where desired) instead of a Values clause for an Insert, eg:
    Code:
    INSERT INTO SecondTable (business_id, request_id, subject,  send_date,  body,  customer_type_id)
    
    SELECT business_id, 37, 'hello', ...
    FROM FirstTable
    WHERE ....
    (rather than hard-coded values, you can use parameters)

    One thing to watch out for is that if the Select returns more than one row, you will insert multiple into the second table.

  3. #3

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,698

    Re: Get Value from Table Prior to Insert

    The user_id field is the auto-incrementing unique field for the first table, so there shouldn't be any duplicates. But thank you so much, I appreciate the solution!
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,034

    Re: Get Value from Table Prior to Insert

    Hi,

    not sure if I understand what you need.

    here a Insert into 3 Tables
    Code:
    Private Sub Command2_Click()
       Dim sSQL As String
       Dim CT_ID As Long 'AutoID in table tbl_Contact
       Dim AD_ID As Long 'AutoID in table tbl_Adresse
       
          On Error GoTo Fehler
       
          Cn.BeginTrans
          
          sSQL = "Insert Into tbl_Contact (CT_City) Values ('Frankfurt')"
          Cn.Execute sSQL
          CT_ID = GetNewID(Cn)
          
          sSQL = "Insert Into tbl_Adresse (AD_Adresse) Values ('Frankstrasse')"
          Cn.Execute sSQL
          AD_ID = GetNewID(Cn)
          
          sSQL = "Insert Into tbl_Person (PE_Name, PE_CT_ID, PE_AD_ID) " & _
                 "Values ('Chris', " & CT_ID & ", " & AD_ID & ")"
          Cn.Execute sSQL
          
          Cn.CommitTrans
          Exit Sub
          
    Fehler:
          Cn.RollbackTrans
    End Sub
    
    'Get ID from new Record
    'from Access version 2000 and up
    Public Function GetNewID(Cn As ADODB.Connection) As Long
       Dim Rs As ADODB.Recordset
       Dim NewID As Long
          Set Rs = New ADODB.Recordset
          Rs.Open "Select @@Identity As ID", Cn, , , adCmdText
          NewID = Rs.Fields("ID").Value
          Rs.Close
          Set Rs = Nothing
          GetNewID = NewID
    End Function
    or with ADOX to get the next Autoincrement No. from the Table

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width