HikJ HikJ - 6 months ago 15
MySQL Question

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

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

Answer

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