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], 
m.created_at = Time.now(),
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.

[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;

[ajetrumpet]

hey John!! How goes it??

How did the problem with the past go? Man...we spent 100 responses on that.

[johnmerlino]

I took the data, put it in excel spreadsheet, and then simplified it by removing unnecessary characters. Then resorted to a basic join statement to check two tables. Thanks for responses. I did a lot of trial and error.

[ajetrumpet]

what does your data with this problem look like then?

[johnmerlino]

Thanks for response. The table structure looks like this:

Mailers table:

id
mailer_states_id
contacts_first_filter_id



UpdateQuery table

id: 1
TheName: Smith,John
Address: 4566 Berks Avenue
City: Jupiter
State: FL
Zip: 33693

contacts_first_filter table

id: 34563
TheName: Smith,John
addresses: 4566 Berks Avenue
cities: Jupiter
us_states_and_canada: FL
zip_code: 33693

What I'm trying to do is check where the address, city, and zip codes match between UpdateQuery table and contacts_first_filter. And where they match, I want to update contacts_first_filter_id of mailers table with the id of contacts_first_filter table. Also there would be a popup asking the user to manually fill in a number for mailer_states_id.

The purpose of all this is the UpdateQuery table contains a list of names who have received mailers. All of the names are located in contacts_first_filter table. By identifying the id of the person and plugging it into contacts_first_filter_id of the mailers table I can manage the different states of that specific contact, such as whether they received mailer, responded, etc.


Thanks for response.

[ajetrumpet]

that's almost the exact same problem I helped you with last time, isn't it John??

What did you do then?? Did any of my suggestions work at that time, or did you discard all of them?? If I recall, we never did get anything to work...

[johnmerlino]

This is little different. The addresses are exactly the same. So the whole purpose of contacts_first_filter table is just to grab its id and stick it into the mailers table. Howeever, in order to know what id to grab I reference the updatequery table to find addresses that match between updatequery and contacts_first_filter.