CODEWITHSUNDEEP
mysqlsql-update
AlexMorley-Finch
AlexMorley-Finch

Reputation: 6975

sql update with joined tables

Say I have this select statement:

SELECT ut.user_id,
       a.firstname,
       a.surname,
       u.username,
       u.email_address
  FROM administrators a 
  JOIN user_types ut JOIN users u ON a.admin_id = ut.type_id 
   AND u.user_id = ut.user_id AND ut.user_type = 'ADMIN';

Say I wanted to update ALL values in the row where user_id = 1;

I could update EACH TABLE individually OR i could create a view like so:

CREATE OR REPLACE VIEW full_admin AS
SELECT ut.user_id,
       a.firstname,
       a.surname,
       u.username,
       u.email_address
  FROM administrators a 
  JOIN user_types ut 
  JOIN users u  on a.admin_id = ut.type_id 
   AND u.user_id = ut.user_id 
   AND ut.user_type = 'ADMIN'

Then I could write:

UPDATE full_admin
   SET firstname = 'blah', etc, etc
 WHERE user_id = 1;

And that will update all rows in all tables

Is there a way to do this WITHOUT creating a view?

Something like:

UPDATE (
    SELECT ut.user_id,
           a.firstname,
           a.surname,
           u.username,
           u.email_address
      FROM administrators a 
      JOIN user_types ut 
      JOIN users u ON a.admin_id = ut.type_id 
       AND u.user_id = ut.user_id 
       AND ut.user_type = 'ADMIN'
)
AS temp_table
  SET firstname = "ALEX"
WHERE user_id = 1;

However that doesnt work

Upvotes: 0

Views: 292

Answers (3)

dash
dash

Reputation: 91550

If you want to update columns in both administrators and users at once, then something like this should do the trick:

UPDATE administrators a  
        INNER JOIN user_types ut ON a.admin_id = ut.type_id 
        INNER JOIN users u ON u.user_id = ut.user_id 
            SET u.firstname = "ALEX", a.SomeColumn = 'X' 
        WHERE u.user_id = 1 
                 AND ut.user_type = 'ADMIN'

The SET statement section can work on any of the columns in the join.

(If that's what you are asking!)

Upvotes: 1

unludo
unludo

Reputation: 5016

You may do some scripting (PLSQL) but I don't believe you may update several tables in one SQL query other than through a view.

PLSQL is powerfull and not complicated and can be triggered by database events.

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65342

UPDATE administrators a 
INNER JOIN user_types ut ON a.admin_id = ut.type_id
INNER JOIN users u ON u.user_id = ut.user_id AND ut.user_type = 'ADMIN'
SET u.firstname = "ALEX"
WHERE u.user_id = 1
;

Ofcourse this will only work (same thing applies to the view) if the joined table construct is updateable.

Upvotes: 0

Related Questions