How to update a timestamp column whilst making a select to another table

Question

I have 2 tables:

CREATE TABLE IF NOT EXISTS t1
(
    id UUID NOT NULL,
    last_login TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS t2
(
    id UUID NOT NULL,
    t1_id UUID NOT NULL,
    PRIMARY KEY (id),

    CONSTRAINT ids
        FOREIGN KEY (t1_id)
            REFERENCES t1 (id)
            ON DELETE CASCADE
            ON UPDATE NO ACTION

);

and I want to select a row from t2 and update the last_login timestamp of the corresponding t1 id in the t1 table.

Is it possible to do this in a single query?

Answer 1

You can't both UPDATE and SELECT in the same SQL operation. You have some options though:

1. Do it at the application level - update the login, then select the data
2. Do it as a SPROC / function that you can execute where both operations are executed in sequence with possible rollback if needed
3. Use triggers to update logins independently
4. Combine the tables to simplify things if they're always 1 to 1 anyway