0

Hello I have a simple question. Lets say I have a user table with columns like userId and username and then I have a table "notifications" and there I have as well columns like userId and username and other.

So my question is - am I able to somehow just INSERT userId value into notifications and the username value will be inserted automatically based on the userId in the users table. Its because I dont wanna somehow access the users table before inserting the notification I think its useless waste. Is something like that possible with MySQL?

Improve this question
2
  • 1
    The better solution would be to remove the username column from the notifications table. Commented Jun 6, 2014 at 16:21
  • i mean sometime i need to read the username from somewhere i think it might just be better if it all was in the notification table - 1 select with +1 column or 2 selects with -1 column Commented Jun 6, 2014 at 16:25

2 Answers 2

Reset to default
2

This trigger inserts data in the notifications table automatically.It is triggered whenever a new row is inserted in user table.

delimiter #

create trigger insert_name after insert on user
for each row
begin
  insert into notifications (id, user_name) values (select id,user_name from user);
end#

Note:

Triggers are best way to handle situations were you want to automate a deletion or insertion task in a database.The reason why people don't use it is because it's a bit difficult to implement for large number of tables and also it's a bit slow and you need to update it every time when you change the table schema.

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

3 Comments

@tadman Yeah I know but the user wants to know whether it's possible or not.
Yeah but something like insert into notifications(id,name) select 1,username from user where userid=1; would be a better attempt
ok thats messy yeah, so there isnt some relation - foreign key or anything that would populate it automatically?
1

If you can compose a SELECT query that has the correct results, you can just chain this in to an INSERT:

INSERT INTO notifications (user_id, user_name)
  SELECT id, name FROM users

Basically the INSERT INTO statement allows the data to be inserted to come from an arbitrary SELECT. So long as the columns match up it should work out fine.

If you're looking to update a table that has the ID of the user but no name:

UPDATE notifications, users
  SET notifications.user_name=users.name
  WHERE notifications.user_id=users.id

If you're looking to do this automatically you will need to create a TRIGGER.

Normally this sort of denormalization is done at the point where you have all the data needed to compose the notification and still have data on the user loaded.

Improve this answer

5 Comments

ok thanks, when i tried to google it i read about triggers so that may be a solution.
@tadman The user wants to enter data only when an id is entered in a notifications table.
Triggers are usually a last resort when you've exhausted all other options. They can lead to deeply mysterious behaviour in your application and are often a nuisance to maintain. If you've got any other way of making this happen, I'd try that first. Also remember that as soon as you denormalize you immediately have sync problems and will need to write a tool to update this duplicated data if users can change their names.
ok so do you think that just 1 more select is better than a trigger right?
It depends on what data you have on hand when you're inserting the notification record. If a user is creating this and you have the user record loaded, which is common in any application framework, then just push in the user's name at that point.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.