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