Syntax error in query expression 'SELECT update_mailer_step_two.id FROM...

[johnmerlino]

I get the above error with this query:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
VALUES (SELECT update_mailer_step_two.id
FROM update_mailer_step_two, 1, Now());

I'm not only trying to pull data from another table and insert it into one, but I'm also trying to give some default values for other fields that belong to the same record as the one we are pulling in.

Thanks for response.

 

[DCrake]

you do not seem to be selecting the correct data items to match the values.

 

[johnmerlino]

What I think the problem is is that you can't use VALUES and SELECT like that. But I don't know another way around it to place the values from the record in one table, while giving default values as well.

 

[johnmerlino]

I try this:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
VALUES ((SELECT update_mailer_step_two.id
FROM update_mailer_step_two), (SELECT mailer_states.id FROM mailer_states WHERE mailer_states.mailer_state = 'sent'), Now());

and get the error:

'Query input must contain at least one table or query'

 

[mcalex]

To reduce the complexity a bit, perhaps you could try getting the data into variables and then updating the data in a separate query

eg setup CFF_ID and MS_ID variables, then run queries such that:

CFF_ID = SELECT update_mailer_step_two.id
FROM update_mailer_step_two

MS_ID = SELECT mailer_states.id FROM mailer_states
WHERE mailer_states.mailer_state = 'sent'

Then your insert query can substitute CFF_ID and MS_ID instead of the queries.

hth
mcalex

 

[johnmerlino]

Thanks for response. I'm not sure how to set variables in Access. I try this in VBA but when clicking button it displays a popup telling me "Query input must contain at least one table or query":

Private Sub Command6_Click()


        Dim CFF_ID As String, MS_ID As String, strSQL As String
 
        CFF_ID = "SELECT update_mailer_step_two.id FROM update_mailer_step_two"

        MS_ID = "SELECT mailer_states.id FROM mailer_states WHERE mailer_states.mailer_state = 'sent'"

        strSQL = "INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )VALUES ((" & CFF_ID & "),(" & MS_ID & "),NOW())"
 
        DoCmd.RunSQL strSQL

End Sub

Thanks for response.

 

[mcalex]

I didn't know about docmd.runsql - will have to check it out. I use QueryDef.Execute

You are on the right track, but you need to run the SELECT queries then the INSERT query. Something like:

dim db as DAO.Database, qdf as DAO.QueryDef, rst as DAO.RecordSet
 
Dim CFF_ID As Long
Dim strCffSQL As String
Dim rows
 
strCffSQL = "SELECT update_mailer_step_two.id FROM update_mailer_step_two"
 
set db = currentDB
set qdf = db.CreateQueryDef("", strCffSQL)
set rst = qdf.OpenRecordset
rows = rst.GetRows()
CFF_ID = rows(0, 0)

will get the CFF_ID variable populated with a value (and assumes that the select query is only returning one value. You may want to check that this is the case - this is only a single row insert, yeah?)

Do something similar to find MS_ID. I think you can reuse the QueryDef and RecordSet

Then your Insert query should work.

cheers
mcalex

 

[johnmerlino]

Thanks for response. This is not a single row insert. I want it to go the length of update_mailer_step_two table, so if there are 20 records in update_mailer_step_two table, then 20 records will be created in mailers, each having the current time, a mailer_state of 'sent', and whatever the current value of id (each record has a different id value) of update_mailer_step_two table. Thanks for response.

This below works but only inserts a single record:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
VALUES (DLookup("id","update_mailer_step_two"), DLookup("id","mailer_states", "mailer_state = 'sent'"), Now());

 

[johnmerlino]

I tried using INNER JOIN but while this produces no error, it appends 0 rows, presumably because there is no INNER JOIN link between update_mailer_step_two and mailer_states:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
SELECT update_mailer_step_two.id, mailer_states.id, Now()
FROM update_mailer_step_two INNER JOIN mailer_states ON update_mailer_step_two.id = mailer_states.ID
WHERE mailer_states.mailer_state = 'sent';

mailer_states table is just a table that contains a mailer_state field with text. It's pretty much just a lookup table that never changes.

update_mailer_step_2 contains one field called id, which contains a bunch of ids associated with contacts in database. Hence, there's no relationship at all between mailer_states and update_mailer_step_2.

INNER JOIN won't work. Any other solution?

 

[mcalex]

Okies, that requires a rethink

If you can get the data you want into a query, then you can insert the contents of the query thusly:

INSERT INTO tblTarget (Column1, Column2, Column3)
SELECT * FROM (<<Your Select Query>>)

where <<Your Select Query>> is the query that returns the data you want added (eg: select col1, col2, col3 from table1 join table2 on id = id)

The query needs to return the same number of columns and of the same type as you want to insert into the table.

hth
mcalex