MySQL multi update error

Question

I have joined three tables into a view, and then i try to update the view and get this error

#1393 - Can not modify more than one base table through a join view

ER_VIEW_MULTIUPDATE

I understand the error. I cannot update multiple tables at once.

HOWEVER... I have done this before. Last week to be precise, on the same machine, same mysql installation.

I promise you that I have joined together tables through a view before (the same tables infact) and updated the view with no problems.

Does anybody know why its not working?

The PHP code that worked last week

"UPDATE administrators AS a INNER JOIN user_types AS ut ON a.admin_id = ut.type_id INNER JOIN users AS u ON u.user_id = ut.user_id SET a.firstname = '{$user_input["firstname"]}', a.surname = '{$user_input["surname"]}', u.email_address = '{$user_input["email_address"]}' WHERE u.user_id = {$user_input["user_id"]}"

The SQL code im trying now

CREATE VIEW admin_users AS
SELECT administrators ad
    JOIN user_types ut
        ON ad.admin_id = ut.type_reference
    JOIN users us
        ON ut.user_id = us.user_id
WHERE ut.user_type = 'ADMIN'

UPDATE admin_users
SET
    firstname = 'alex2',
    surname = 'finch2',
    email_address = '[email protected]'
WHERE
    user_id = 2

It would be helpful if you would post some code. The SQL for the view and for the UPDATE query. I'm guessing your UPDATE is modifying fields that belong to more than one table, but no way to tell without seeing some code. — kitti
– kitti, Commented Feb 24, 2012 at 15:23
It may be a permission problem with one of the tables and the user you are using today, which may be different from the user you used "last week". — vulkanino
– vulkanino, Commented Feb 24, 2012 at 15:26

kitti · Accepted Answer · 2012-02-24 15:47:17Z

2

The SQL that worked last week was a direct UPDATE query that modified tables, whereas you're now attempting to UPDATE those same tables through a view. That's the change. With a VIEW, you can only update one table at a time. You might look into stored procedures if you want to update multiple tables with one query.

Something like this:

DELIMITER //
CREATE PROCEDURE updateUser(
    var_user_id INT,
    var_firstname VARCHAR(32),
    var_surname VARCHAR(32),
    var_email_address VARCHAR(128)
)
BEGIN
    UPDATE administrators AS a 
        INNER JOIN user_types AS ut 
            ON a.admin_id = ut.type_id 
        INNER JOIN users AS u 
            ON u.user_id = ut.user_id 
        SET a.firstname = var_firstname, 
            a.surname = var_surname, 
            u.email_address = var_email_address 
    WHERE u.user_id = var_user_id;
END//

To call:

CALL updateUser( '$user_id', '$firstname', '$surname', '$email_address' );

edited Feb 24, 2012 at 15:47

answered Feb 24, 2012 at 15:41

kitti

14.9k33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AlexMorley-Finch Over a year ago

Ahh... I only really made the view so the code was more readable. Thanks

Collectives™ on Stack Overflow

MySQL multi update error

The PHP code that worked last week

The SQL code im trying now

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The PHP code that worked last week

The SQL code im trying now

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related