3iB854iJ

1UPDATE validar_goles SET 
2     a_favor= SUBSTRING_INDEX( goals , ':', 1 ),
3     en_contra= SUBSTRING_INDEX(SUBSTRING_INDEX( goals , ':', 2 ),':',-1) ;
4	
5UPDATE validar_goles AS t2
6    LEFT JOIN validar_goles AS t1 ON (t1.ID = t2.ID)
7    SET t1.decimal_goals = REPLACE(t2.goals,':','.');
8	
9CREATE TABLE IF NOT EXISTS validar_goles 
10(
11    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
12    goals VARCHAR(10),
13    decimal_goals DECIMAL(10,2),
14    a_favor INT,
15    en_contra INT
16)ENGINE=INNODB;
17
18INSERT INTO validar_goles (goals)
19    VALUES 
20        ('50:14'),
21        ('41:22'),
22        ('30:26')
23;
24
25SELECT * FROM validar_goles;
26
27UPDATE validar_goles SET 
28     a_favor= SUBSTRING_INDEX( goals , ':', 1 ),
29     en_contra= SUBSTRING_INDEX(SUBSTRING_INDEX( goals , ':', 2 ),':',-1) ;
30
31UPDATE validar_goles AS t2
32LEFT JOIN validar_goles AS t1 ON (t1.ID = t2.ID)
33SET t1.decimal_goals = REPLACE(t2.goals,':','.');
34
35SELECT * FROM validar_goles;
36	
37ID  goals   decimal_goals   a_favor     en_contra
381   50:14   NULL             NULL        NULL
392   41:22   NULL             NULL        NULL
403   30:26   NULL             NULL        NULL
41	
42ID  goals   decimal_goals   a_favor      en_contra
431   50:14   50,14            50          14
442   41:22   41,22            41          22
453   30:26   30,26            30          26