What's the most efficient way of truncating the names in a first_name and last_name column to 1 character?

Question

I have a mysql database that I want to hand off to a developer but I want to partially sanitize the data so that the two name records drop down to just initials.

I tried to modify the varchar to 1 character but mysql will not let me truncate the data, it just throws an error. I am working from a dump of the database before I hand it off. I want to obscure the names without making them all the same.

Do the update in chunks to keep transaction size down. Perhaps 10000 - 100000 rows per update. — jarlh, Mar 10 '20 at 19:58

score 1 · Accepted Answer · answered Mar 10 '20 at 19:14

1

update tablename set first_name=left(first_name, 1), last_name = left(last_name, 1)

But, as Gordon Linoff mentioned, this could be expensive if you have a lot of rows in the table

answered Mar 10 '20 at 19:14

Kevin

7,162
11
46
70

I like this answer and it works but I keep getting this error `ERROR 1048 (23000): Column 'user_id' cannot be null` But I don't get that at all because we aren't editing the user_id in the query and the weirdest part is that when I display the records again it executed properly for 1 set of records. have you seen anything like that before? – Tenthrow Mar 10 '20 at 19:45
To make this even weirder, I ran the query about 20 times and at some point it finished all the rest without complaining. I can't figure out what that's about. – Tenthrow Mar 10 '20 at 19:47
@Tenthrow No idea why you would have been getting that error – Kevin Mar 10 '20 at 20:01
It looks like something caused by a trigger, but I didn't design this database so I didn't know it was there. Go figure. You're query worked great! Thank you! – Tenthrow Mar 10 '20 at 20:22
@Tenthrow Yeah, I was actually about to comment that it looked like something that might be caused by a constraint or a trigger – Kevin Mar 10 '20 at 20:24

score 0 · Answer 2 · answered Mar 10 '20 at 19:10

0

Updating all the rows is rather expensive because of the logging required. The most efficient way is probably to create a new table:

create table for_developer as
    select left(first_name, 1) as first_name, left(last_name, 1) as last_name,
           . . . -- rest of columns
    from t;

answered Mar 10 '20 at 19:10

Gordon Linoff

1,242,037
58
646
786

Would I be able to replace the original table like that? I need to result to have identical structure to the production database. – Tenthrow Mar 10 '20 at 19:14
@Tenthrow . . . Just list the rest of the columns. – Gordon Linoff Mar 11 '20 at 00:42

score 0 · Answer 3 · answered Mar 10 '20 at 19:19

0

Why don't you try:

Update "you_table"
SET first_name = LEFT(first_name,1), 
last_name = LEFT(last_name,1);

answered Mar 10 '20 at 19:19

Erick

301
3
12

Radagast · Answer 4 · 2020-03-10T19:53:56.580

You could use like when creating a new table so you have the same column attribute and definitions as the old table. Be sure to check the manual on the behavior of like when creating tables though, especially if you are concerned about indexing.

create table new_table like old_table;

insert into new_table

select left(first_name, 1) as first_name, left(last_name, 1) as last_name, ..-- rest of columns
from old_table;

What's the most efficient way of truncating the names in a first_name and last_name column to 1 character?

4 Answers4