CODEWITHSUNDEEP
phpmysqlpdo
fekiri malek
fekiri malek

Reputation: 376

MySQL: Get last update id from a table to insert it in an other table

basically I'm trying to update a table and in case of something changed to save the unique id of the changed row in an other table (with some other informations).

it's almost the same principle as LAST_INSERT_ID() but for update not insert. I searched in Internet and the best what i got is this statement:

SET @update_id := NULL;
UPDATE friendships SET status = ?, id = (SELECT @update_id := id) 
       WHERE (from_user = ? AND to_user = ?) OR (to_user = ? AND from_user = ?);
INSERT INTO actions(user_id, code, foreign_id) VALUES(?, ?, SELECT @update_id);

all this statment is passed all together to a PDO prepare execute function. i don't have an error back but i have the first table updated and nothing inserted in the second.

@update_id is supposed to hold the id of the changed row in the first query to be used in the second one.

i can't use a trigger on the first table to update automatically the second because i have to insert the "code" in the second table and can't pass it in the first query (since it's not used or inserted in the first table).

tried my best to explain the situation.. hope u understand me and can help me too

much thank in advance

Upvotes: 2

Views: 1124

Answers (2)

Harish Lalwani
Harish Lalwani

Reputation: 774

use the mysql TIMESTAMP column with "ON UPDATE CURRENT_TIMESTAMP" set. You can sort by this column to get the recently modified record.

Upvotes: 0

Mihai
Mihai

Reputation: 26784

There are 2 ways to insert INSERT...SELECT and INSERT..VALUES,dont mix them,remove the SELECT in the last query

INSERT INTO actions(user_id, code, foreign_id) VALUES(?, ?,@update_id);

Upvotes: 1

Related Questions