CODEWITHSUNDEEP
mysqlsqlsubstring
goonerboi
goonerboi

Reputation: 319

Substring not working in MySQL

I have a table in MySQL from which I'm extracting the first letter and inserting it into another column which is empty but its not extracting properly. Its showing null values and doubling the no of rows in the table and showing it in the other half like below.

TEAM              TS

AUSTRALIA         (NULL)
ZIMBABWE          (NULL)
GHANA             (NULL)
(NULL)             A
(NULL)             Z
(NULL)             G

But I want it in the following manner:

TEAM              TS

AUSTRALIA         A
ZIMBABWE          Z
GHANA             G

I have written the following query:

insert into [table_name]
 (
 TS
 ) 
 SELECT  SUBSTRING(TEAM,1,1)   
     FROM [table_name]
;

What am I doing wrong?

Upvotes: 0

Views: 5675

Answers (2)

contremaitre
contremaitre

Reputation: 106

Insert Add a new entry in table, and you specify TS column only, so the team column takes null value. You should use UPDATE :

UPDATE [table_name]
SET table_name.TS = (SELECT  SUBSTRING(B.TEAM,1,1)   
 FROM (SELECT TEAM FROM [table_name]) AS B))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You want update. I would write this as:

update table_name
    set ts = left(team, 1);

insert inserts new rows. update changes the values in existing rows. That is why you are getting additional rows with NULL values for the team.

Upvotes: 1

Related Questions