MySQL select 3 first characters and replace

Question

I have a simple problem, which is causing me problems

I have a column in mysql with names, and I'd like to replace these by the first three characters in the name. So for instance 'Jacob' -> 'Jac'.

I know of the LEFT command, which can do this for me, but I can't seem to update the column with the output. Like I can do a select, and then get the correct output, but I can't update the desired column with the output :(

I tried:

REPLACE INTO mytable(shortnames) SELECT LEFT(names,3) FROM mytable;

Show us the select query and the update query you have tried — RiggsFolly, Sep 05 '17 at 12:39
`replace into` statement inserts a new record, or in case of a duplicate conflict, deletes the old record and inserts the new one. — Shadow, Sep 05 '17 at 12:43

score 2 · Accepted Answer · answered Sep 05 '17 at 12:42

2

I think you need

UPDATE mytable SET names = LEFT(names,3);

answered Sep 05 '17 at 12:42

kinggs

1,162
2
10
25

No problem. Please consider marking this as the answer if it resolved your problem. Thanks – kinggs Sep 05 '17 at 12:51

RiggsFolly · Answer 2 · 2017-09-05T12:47:48.510

0

This can be done with a simple UPDATE query

UPDATE table SET names = LEFT(names,3)

However it might be a good idea to create another column at least temporarily and do

UPDATE table SET short_name = LEFT(names,3)

Then either keep both columns or drop the column you dont want any more.

Of course you could leave the name as it is and when you only want the short version just select it like that from the names column like this

SELECT LEFT(names,3) as short_name from table

edited Sep 05 '17 at 12:47

answered Sep 05 '17 at 12:43

RiggsFolly

93,638
21
103
149

Thanks for your help! – 12344343243 Sep 05 '17 at 12:45

score 0 · Answer 3 · answered Sep 05 '17 at 12:44

0

I think this is what you need

UPDATE `table` SET `field` = LEFT(`field`, 3)

answered Sep 05 '17 at 12:44

Manav

1,357
10
17

MySQL select 3 first characters and replace

3 Answers3