How to insert a query result into a column mysql

Question

ok so I do this mysql command:

SELECT CONCAT(SUBSTRING(Familenaam,1,4),SUBSTRING(Voornaam,1,4)) AS `Login` FROM tblusers

This gives me the result for johny cowbell -_> johncowb (=loginname) but this is in a query.

I want that result in my table tblusers in the column "Login". How do I do this?

Mahmoud Gamal · Accepted Answer · 2012-11-10T00:24:09.800

4

You can use INSERT INTO .. SELECT like so:

INSERT INTO tblusers(login)
SELECT 
  CONCAT(SUBSTRING(Familenaam, 1 ,4), SUBSTRING(Voornaam,1,4)) 
FROM tblusers

If you want to update, you can do this:

UPDATE tblusers t1
INNER JOIN
(
   SELECT 
     userid,
     CONCAT(SUBSTRING(Familenaam, 1 ,4), SUBSTRING(Voornaam,1,4)) login
   FROM tblusers
) t2 ON t1.userid = t2.userid
SET t1.login = t2.login

edited Nov 10 '12 at 00:24

answered Nov 10 '12 at 00:11

Mahmoud Gamal

78,257
17
139
164

Thanks! But I actually need an UPDATE of the current users, do you know how to do that? – John Smittheeerrs Nov 10 '12 at 00:19
@JohnSmittheeerrs - Yes, see my edit. Note that, I used `userid` as the condition for the join, you can use other columns as well based on the table structure. – Mahmoud Gamal Nov 10 '12 at 00:25
Ok thanks, I'm going to give it a whirl :p EDIT: It works! Thanks a lot :D – John Smittheeerrs Nov 10 '12 at 00:26

score 1 · Answer 2 · answered Nov 10 '12 at 00:11

1

You would do:

INSERT INTO tblusers (Login)
SELECT CONCAT(SUBSTRING(Familenaam,1,4),SUBSTRING(Voornaam,1,4)) AS `Login` FROM tblusers;

This is essentially saying "insert the result of my 'SELECT' query into the 'login' column of table 'tblusers'.

answered Nov 10 '12 at 00:11

mcriecken

3,217
2
20
23

Thanks! But I actually need an UPDATE of the current users, do you know how to do that? – John Smittheeerrs Nov 10 '12 at 00:18
I'm not sure what you mean. You want to update the current user's login name? – mcriecken Nov 10 '12 at 00:19
The users at this moment have no loginname yet. So next to the First name and last name column there has to come a login column with the loginame. (the columon is already made but the value is still empty) – John Smittheeerrs Nov 10 '12 at 00:21

How to insert a query result into a column mysql

2 Answers2

Linked