-
Feb 8th, 2018, 05:22 PM
#1
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.
-
Feb 8th, 2018, 05:42 PM
#2
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.
-
Feb 8th, 2018, 05:54 PM
#3
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!
-
Feb 9th, 2018, 02:48 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|