MySQL substring_index Delete Everything In Field After Space

Question

I have a table called subscribers with a name field that contains data like this:

Tom Jones

Drew Brees

Tom Brady III

I need to delete everything after the first space for each record so that it looks like:

Tom

Drew

Tom

Using this question, I assembled the following query:

select substring_index(name,' ',1) as deleteAllAfterSpace from subscribers

I get the following error:

Current selection does not contain a unique column.

When I run: SELECT * FROM subscribers WHERE INSTR(name, ' ') > 0

I do get the values I'm looking for as described.

I can assure you, name column is unique? What am I doing wrong?

Can you provide more context? Maybe `DESCRIBE subscribers` output? And are there only those 3 rows in the table? Does this help? https://stackoverflow.com/questions/18922503/this-table-does-not-contain-a-unique-column-grid-edit-checkbox-edit-copy-and — richyen, Mar 11 '21 at 22:33
@richyen there is a Primary Key on a column called id. When I run a SELECT * FROM `subscribers` WHERE INSTR(`name`, ' ') > 0 I get the records I'm looking for. — Rocco The Taco, Mar 11 '21 at 22:35
I think this is an error of the grid view client you're using. It wants every row to have some column that can uniquely identify the given row. But the one column you are returning has duplicates. Maybe you are using phpMyAdmin? — Bill Karwin, Mar 11 '21 at 22:37
you are niot selecting you are uzpdatein and name is unique or primary key, and so tom can be there only once — nbk, Mar 11 '21 at 22:41
@BillKarwin yes...that is the only access I have. Is there a work around in phpMyAdmin? — Rocco The Taco, Mar 11 '21 at 22:45
Include some unique column in the select-list: `SELECT id, substring_index(name,' ',1) as deleteAllAfterSpace FROM subscribers WHERE INSTR(name, ' ') > 0` — Bill Karwin, Mar 11 '21 at 22:46
@BillKarwin that selects them but does not update each record? Weird? — Rocco The Taco, Mar 11 '21 at 22:51

score 1 · Accepted Answer · answered Mar 12 '21 at 01:35

1

SELECT name, subString(name, 1, POSITION(' ' IN name)) as deleteAllAfterSpace 
FROM subscribers;

Here is all you n

answered Mar 12 '21 at 01:35

This does not actually make the changes to the column data for each record. It only displays the data. – Rocco The Taco Mar 12 '21 at 10:14
Issue was with myPHPAdmin, ran this natively and it was fine. Thanks! – Rocco The Taco Mar 12 '21 at 23:49

1 Answers1