SQL UPDATE with JOIN (or VIEW) with WHERE clause

First of all, let me preface the post by stating I am nowhere near pro with SQL.

I have specific problem where I have to update 1 table using WHERE condition from another table, and while I somewhat know how to do it, I was wondering if there is more elegant way of doing it, since what I do doesn’t feel right or fast enough, as I am nesting SQL statements.

Maybe there is no better way I don’t know, I just suspect there is since nesting makes me think that query runs 2 times slower cause it executes 2 queries not 1. I could be all wrong, as I said I am not pro…

Additionally, I tried searching on google for answers like
UPDATE with JOIN and WHERE and similar terms, but I seem to get completely different examples which are far from what I am trying to do. I failed cause maybe I do not know what to search for properly, that is why I am asking here.

My example is:

START TRANSACTION;
SAVEPOINT SP0;

UPDATE api_keys AS a
SET a.api_key = "NEW API KEY"
WHERE a.id = (
                 SELECT u.id FROM users AS u WHERE u.user_key = "SOME USER KEY"
             );

ROLLBACK TO SAVEPOINT SP0;
COMMIT;

What it means is, I am running separate query to supply value for WHERE condition for another query. What I expected to be possible to do is:

Something like this:

UPDATE api_keys AS a
SET a.api_key = "NEW API KEY"
FROM u
INNER JOIN users AS u
ON a.id = u.id
WHERE a.id = u.id;

I know it is wrong it is just simple example to illustrate my thinking, I am not sure if I think exactly that way, but I was thinking of some way to make join or something without executing sub-query.

You need to sign in to view this answers