nested joins to update one table based on the comparison between two other tables

[johnmerlino]

Hey all,

I have a table called mailers which I want to update the mailer_states_id with a user required input and update contacts_first_filter_id field with the id of the contact from the contacts_first_filter table whose address, city, and zip code (in the contacts_first_filter table) is equal to the address, city, and zip code of the update query table. The reason is because the update query table has the people who are in the contacts_first_filter table, so I want to update the contacts_first_filter_id of mailer with the id of that contact from contacts_first_filter.

This is query I have:

UPDATE mailers AS m INNER JOIN
(UpdateQuery INNER JOIN contacts_first_filter 
ON
UpDateQuery.Address = contacts_first_filter.addresses
AND
UpdateQuery.City = contacts_first_filter.cities
AND
UpdateQuery.Zip = contacts_first_filter.zip_code
)
SET m.mailer_states_id = [user input] 
AND
SET m.created_at = Time.now()
AND
SET m.contacts_first_filter_id = contacts_first_filer.id;

I get syntax error in update statement and it highlights the first SET. I assume it's an issue with the nested joins.

Thanks for response.

 

[mcalex]

try:

update ...
set m.mailer_states_id = [user input], 
  m.created_at = Time.now(), 
  m.contacts_first_filter_id = contacts_first_filer.id;

you may also want a where clause


hth
mcalex

 

[johnmerlino]

Thanks for response. A where clause for where?

 

[mcalex]

A where clause to only change the record that the user is working with. Something along the lines of:

where m.mailer_id = <the id of the record>

at the end of the sql.

hth
mcalex

 

[johnmerlino]

This still gives same syntax error:

UPDATE mailers AS m INNER JOIN
(UpdateQuery INNER JOIN contacts_first_filter 
ON
UpDateQuery.Address = contacts_first_filter.addresses
AND
UpdateQuery.City = contacts_first_filter.cities
AND
UpdateQuery.Zip = contacts_first_filter.zip_code
)
SET m.mailer_states_id = [user input], 
m.created_at = Time.now(),
m.contacts_first_filter_id = contacts_first_filer.id;

Thanks for response

 

[johnmerlino]

This doesn't work either:

 UPDATE mailers SET mailers.contacts_first_filter_id = contacts_first_filer.id FROM mailers INNER JOIN contacts_first_filer, UpdateQuery ON contacts_first_filer.addresses = UpDateQuery.Address,
contacts_first_filer.cities = UpDateQuery.City,
contacts_first_filer.zip_code = UpDateQuery.Zip;

 

[mcalex]

Ok, I'm not sure then. The only suggestion I would make is to rip out the 'AS m' from the select line, and all the 'm.' from the set lines. You can only update one table at a time, so they _shouldn't_ be needed.

You may have to post a copy of the database for some of the experts/ gurus here (i'm barely at journeyman level ). Sorry I couldn't help more.

cheers
mcalex