0

I'm using

SELECT m.id, r.username AS `from`, s.username AS `to`, m.message
FROM msgs m
INNER JOIN usrs r ON r.user_id = m.from
INNER JOIN usrs s ON s.user_id = m.to;

to replace user ids with user names in the 'from' and 'to' columns of messages and then output it to a file with mysql usrmsgs < script.sql > output.txt

I'd like to replace the user ids in 'from' and 'to' in the msgs table itself with the corresponding user names from usrs. What's the best way to do this?

imgdat
  • 9
  • 1
  • Also ... https://stackoverflow.com/q/9894353/2943403 and https://stackoverflow.com/q/9957171/2943403 and https://stackoverflow.com/q/1387395/2943403 and https://stackoverflow.com/q/806882/2943403 and https://stackoverflow.com/q/8331687/2943403 and https://stackoverflow.com/q/1293330/2943403 and https://stackoverflow.com/q/3151919/2943403 – mickmackusa Oct 05 '21 at 23:07

1 Answers1

0
alter table msgs
    add column from_name varchar(<size>),
    add column to_name varchar(<size>);

update msgs m 
join usrs r ON r.user_id = m.from
join usrs s ON s.user_id = m.to
SET m.from_name = r.username, m.to_name = s.username;

alter table msgs drop column from, drop column to;
Rick James
  • 135,179
  • 13
  • 127
  • 222
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you! That works however when dropping the columns `from` and `to` need to be backticked. I was hoping I could do this without adding a new column though. I might just keep the old from and to columns anyway. – imgdat Nov 25 '12 at 09:49
  • I assumed the old columns were integers, not strings. – Barmar Nov 25 '12 at 09:50
  • Yeah I should have stated the column types. `from` and `to` are varchar(25), user_id is int(10), and username is varchar(50) – imgdat Nov 25 '12 at 10:28
  • So you at least need to modify the size of `from` and `to` to `varchar(50)`. Then you can assign to the same columns instead of adding new columns. – Barmar Nov 25 '12 at 10:30