sql update with joined tables

Question

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

If you want to update columns in both administrators and users at once then look at this: http://stackoverflow.com/questions/6362594/a-conditional-sql-update-statement-for-two-tables-at-once. If you want to just update columns in administrators based on a join to users then look at this: http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql. — dash, Feb 08 '12 at 13:24

score 1 · Accepted Answer · answered Feb 08 '12 at 13:30

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!)

score 0 · Answer 2 · answered Feb 08 '12 at 13:17

0

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.

answered Feb 08 '12 at 13:17

Eugen Rieck

64,175
10
70
92

Not all JOINS are updateable, e.g. LEFT JOINS will not work (The DB has no PK to address a row). This is exactly the same for Views. – Eugen Rieck Feb 08 '12 at 13:35

score 0 · Answer 3 · answered Feb 08 '12 at 13:20

0

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.

answered Feb 08 '12 at 13:20

unludo

4,912
7
47
71

sql update with joined tables

3 Answers3