I have a problem with the 'UPDATE' command in MySQL

Question

i have a question about the "UPDATE" in my sql; I have these datas:

create database execicios
default character set utf8
default collate utf8_general_ci;


create table if not exists contas_a_receber(
 Nome char(40),
 Numero int not null,
 Valor decimal(10,2),
 Vencimento date,
 Banco char(10),
 primary key (Numero)
)default charset = utf8;


insert into contas_a_receber values
('ABC PAPELARIA', '100100', '5000.00', '2017-01-20', 'ITAU'),
('LIVRARIA FERNANDES', 100110, 2500.00, '2017-01-22', 'ITAU'),
('LIVRARIA FERNANDES', '100120', '1500.00', '2016-10-15', 'BRADESCO'),
('ABC PAPELARIA', '100130', '8000.00', '2016-10-15', 'SANTANDER'),
('LER E SABER', '200120', '10500.00', '2018-04-26', 'BANCO DO BRASIL'),
('LIVROS E CIA', '200125', '2000.00', '2018-04-26', 'BANCO DO BRASIL'),
('LER E SABER', '200130', '11000.00', '2018-09-26', 'ITAU'),
('PAPELARIA SILVA', '250350', '1500.00', '2018-01-26', 'BRADESCO'),
('LIVROS MM', '250360', '500.00', '2018-12-18', 'SANTANDER'),
('LIVROS MM', '250370', '3400.00', '2018-04-26', 'SANTANDER'),
('PAPELARIA SILVA', '250380', '3500.00', '2018-04-26', 'BANCO DO BRASIL'),
('LIVROS E CIA', '453360',  '1500.00', '2018-06-15', 'ITAU'),
('LIVROS MM', '453365', '5400.00', '2018-06-15', 'BRADESCO'),
('PAPELARIA SILVA', '453370', '2350.00', '2017-12-27', 'ITAU'),
('LIVROS E CIA', '453380', '1550.00', '2017-12-27', 'BANCO DO BRASIL'),
('ABC PAPELARIA', '980130', '4000.00', '2016-12-11', 'ITAU'),
('LIVRARIA FERNANDES', '770710', '2500.00', '2016-11-15', 'SANTANDER'),
('ABC PAPELARIA', '985001', '3000.00', '2016-09-11', 'ITAU'),
('PAPEL E AFINS', '985002', '2500.00', '2016-03-12', 'SANTANDER'),
('LER E SABER', '888132', '2500.00', '2017-03-05', 'ITAU');

I wanna change all the words wrote 'SANTANDER' for 'BANCO DO BRASIL' I was try use the command UPDATE, but is not worked

The command that i used is:

set Banco = 'BANCO DO BRASIL'
where Banco like 'SANTANDER';

Somone can help me?

Your Banco column is a char(10), and BANCO DO BRASIL is larger than 10 characters... — Anis R.
– Anis R., Commented Apr 3, 2020 at 20:28

Felippe Duarte · Accepted Answer · 2020-04-03 20:39:29Z

1

Use UPDATE contas_a_receber set Banco = 'BANCO DO BRASIL' where Banco like 'SANTANDER%';

The column Banco datatype is char(10) not varchar(10), which means the database will fill with blank until have 10 chars, like "SANTANDER ".

One more thing, BANCO DO BRASIL has more than 10 char, so it will crop or fail. You need to change it to allow more than 10 chars:

ALTER TABLE contas_a_receber MODIFY Banco varchar(50)

But make sure it will have no implications in your system.

edited Apr 3, 2020 at 20:39

answered Apr 3, 2020 at 20:29

Felippe Duarte

15.2k2 gold badges27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I have a problem with the 'UPDATE' command in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related