How to link one row to a row on another table?

Question

I have a little odd situation here and I'm not going to be able to alter the database structure so please don't mention that.

I have two tables called users and forum_users. 'users' table has two rows: user_id (primary), username. 'forum_users' table has three rows: id (primary), forum_user_id, forum_username.

Say we have a user with user_id of '23'. This user is already automatically added to forum_users table with id of '1', forum_user_id of '23', and a random forum_username. What I want to accomplish is, I want 'forum_username' data to update to 'username' data of the entry where 'user_id' is equal to 'forum_user_id'.

How do I go about accomplishing this? Can this be done through only MySQL without using any kind of external script? If not then how I go about making such script?

Sample database: http://pastebin.com/ZYf8baV3

Here's a quick Paint drawing of what I need: Drawing

Please show what you are trying to explain with sample data and expected results. I can't follow what you're trying to describe. — Siyual, Jun 02 '16 at 19:15
I wasn't asked, I'm just working on a project with little unusual requirements. It's not a forum, I just used that name as placeholder. — Help Leecher, Jun 02 '16 at 19:19
So you want the username in the users table to update whenever an update is made to the username in the forum_users table? — ryebr3ad, Jun 02 '16 at 19:20
I want forum_username in forum_users table to update to username in users table whenever a new entry is added to forum_users table. — Help Leecher, Jun 02 '16 at 19:22
My bet is that @HikJ wants to replicate data on a phpBB forum into another database. That is why he can't change the structure. Why not edit the phpBB function called on username changes so it edits on both tables? — Edu, Jun 02 '16 at 19:33

Jared Stroebele · Accepted Answer · 2016-06-03T20:14:03.160

1

Sounds like you want a Trigger

The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE. It can be invoked before or after the event.

Quick Example using the data you provided:

CREATE TRIGGER CasscadeUsername_after_update
AFTER UPDATE
ON Users FOR EACH ROW

BEGIN

UPDATE forum_users
SET forum_username = username
WHERE forum_users.user_id = users.user_id

END

Here is a link to a tutorial trigger-syntax

edited Jun 03 '16 at 20:14

answered Jun 02 '16 at 19:36

Jared Stroebele

574
6
25

I agree with this solution. You should create a trigger on forum_users. On an update, make it update the line on users to make it equal. – Edu Jun 02 '16 at 19:39
This question is pretty good [How to use update trigger to update another table](http://stackoverflow.com/questions/5252802/how-to-use-update-trigger-to-update-another-table) – Jared Stroebele Jun 03 '16 at 21:15

score 0 · Answer 2 · answered Jun 02 '16 at 19:33

To update all the records of user with corresponding username of forum_user, try using the below code

 update user a
    set username = (select forum_user_name  from forum_user b 
where b.forum_user_id=a.user_id);

To update only for a single record, say 23, use the below update statement

update user a
set username = (select forum_user_name  from forum_user b
 where b.forum_user_id=23)
where user_id=23;

How to link one row to a row on another table?

2 Answers2