How to update with multiple-table syntax?

Question

How can I use the result of a SELECT to determine the fields changed in a subsequent UPDATE. (Effectively chaining the SELECT followed by the UPDATE together in a compound statement).

Here is what I'm trying to do in SQL:

SELECT id_of_record_in_table_B 
FROM table_A 
WHERE table_A.id_of_record_in_table_B = 36;

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_B.id = id_of_record_in_table_B --from the SELECT above)

The case sensitive match is not really relevant to the problem but I've included it because it is actually what I need to do. The problem I am trying to work out is how to "pass" the value returned from the SELECT into UPDATE.

Answer 1

You could it in the same query such as

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_b.id = (SELECT id_of_record_in_table_B 
                  FROM table_A 
                  WHERE table_A.id_of_record_in_table_B = 36)