sql update table column based on values across multiple tables

Question

I have 3 tables (only relevant columns shown with example values):

requests

id website_id
=============
1  NULL
2  NULL
3  NULL

request_fields

id requests_id field_id field_response
=============================================

1  1                  1        some site
2  1                  2        some user
3  2                  1        some other site
4  2                  2        some other user
5  3                  1        some site
6  3                  2        rando user

websites

id website_name
===============
1  some site
2  some other site

My goal is to update requests.website_id to be the value from websites.id, but this value is determined by selecting from request_fields where requests.id=request_fields.requests_idand request_fields.field_id=1 and request_fields.response=websites.website_name

So IOW, for each row in requests, use requests.id to select request_fields.field_resonse from request_fields where request_fields.request_id=requests.id and request_fields.field_id=1 and then select websites.id from websites where websites.name=[the request_fields.field_resonse value] and then set the original requests.id value to that websites.id value.

So end result would be

requests

id website_id
=============
1  1
2  2
3  1

Is this possible to do? I've been trying to wrap my head around it and I think it involves inner joins and stuff but this is a bit much for me.

edit:

This is my current attempt, but I'm not sure I got it right?

update requests
inner join request_fields on requests.id=request_fields.request_id
inner join websites on request_fields.response=website.name
set requests.website_id=websites.id

I'm kind of eyeballing the table and it seems to have worked but again.. I'm a bit over my head here and not sure if I'm about to mess something up on my real table.

Answer 1

You need to join all 3 tables in the UPDATE statement like this:

UPDATE requests r
INNER JOIN request_fields rf ON rf.requests_id = r.id
INNER JOIN websites w ON w.website_name = rf.field_response
SET r.website_id = w.id
WHERE rf.field_id = 1;

See the demo.