I need to update a row in a table after the use of a view in MySQLWorkbench

Question

It's been a while since i used MySQLWorkbench to make queries and i'm kinda lost here. I want to update a row in a table named last_export that refers the last time a specific app had access to the datas in the database. Each app has it's own row so their queries ( made with differents views) feed them the datas they 're missing since their last call.

I tried to make 2 statement in my view, it was like this :

VIEW my_view AS
    SELECT 
my_selected_info
    FROM
        (((my_1_table
        JOIN 2_table ON ((1_id = 2_id1)))
        JOIN  3_table ON ((2_id = 3_id2)))
        JOIN last_export  ON (((2_created > last_export_date)
            OR (2_audited > last_export_date))));
UPDATE last_export SET last_export_date = DATE(NOW()) WHERE export_destination = my_app

But, every time i apply the change into the view, the UPDATE Statement disapear in the code of the view and the view update the last_export_date only one time before going back to only send the datas i want without updating last_export.

It's need to be said but i don't have the admin access to this base, i can only create view, tables and modify the datas inside, and this view is used in another database management tool (AikenWorkbench if it can help) to create another view from the datas of my view.

EDIT (08/07/2024) :

I have new informations :

1- I can't use anything else than a view for the starting point of all my actions (AikenWorkbench,the application that need the info from my select, use directly my view to get the informations needed)

2- I can't install module on the database since it's used by another application that 'owned' it and is very restrictive on what we're allowed to do with it.

3- I have the right to create and modify Function and Stored Procedures

I've tried a little on my own the Stored Procedure and Function but it didn't seems to work. I either could not apply the changes or it respond with an error : 'Error Code: 1442. Can't update table 'last_export' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.'

On another note, i find it weird that my first view work only one time, it should always work or not work in the first place. Seems odd to me, if someone have an explanation ?

The thing you are looking for is called a stored procedure. In this you can write a select and apply it to your update and execute that stored procedure whenever you need to. Although you will need additional permissions for that. Views are only for well.. viewing. They can't update or delete data from tables. — Teun Kruijer
– Teun Kruijer, Commented Jul 5, 2024 at 10:05
This is not the job of the view or that of the database. The application should update the last_export table after calling the view. Views can only contain select statements and CTEs, nothing else. — Shadow
– Shadow, Commented Jul 5, 2024 at 10:06
Ok, thanks for the clarification, i will talk with the admin of the db and see if we can work something out. — Théo Matrod
– Théo Matrod, Commented Jul 5, 2024 at 10:21

Toni · Accepted Answer · 2025-11-26 08:44:38Z

0

MySQL views can only contain a single SELECT statement. Any other statement (UPDATE, INSERT, DELETE, CALL, etc.) is automatically stripped out. That's why your statement keeps disappearing.

To resolve your issue: Create the view, create a stored procedure to update last_export, and use both in your workflow:

CREATE OR REPLACE VIEW my_view AS
SELECT 
    my_selected_info
FROM
    my_1_table
    JOIN 2_table ON (1_id = 2_id1)
    JOIN 3_table ON (2_id = 3_id2)
    JOIN last_export e ON (
        2_created > e.last_export_date
        OR 2_audited > e.last_export_date
    );

CREATE PROCEDURE update_last_export(IN app_name VARCHAR(255))
BEGIN
    UPDATE last_export
    SET last_export_date = NOW()
    WHERE export_destination = app_name;
END

CALL update_last_export('my_app');
SELECT * FROM my_view;

answered Nov 26 at 8:44

Toni

1,6635 gold badges21 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

I need to update a row in a table after the use of a view in MySQLWorkbench

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related