Replace data in a MySQL column

Question

Replace data in a MySQL column

Navigation

#1 by (3 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have the following table Computers

I want to replace only the department column with a text string.

And I am using the following query:

UPDATE computers SET departamento = REPLACE (departamento, (1), "Administración");
UPDATE computers SET departamento = REPLACE (departamento, (2), "Contabilidad");

Where if the value of the department is 1 , it replaces it with " Administration ", if it is 2 , it replaces it with "< strong> Accounting ".

Up there, all right.

The problem comes when I have 11 in the department, and replaces it with " AdministracionAdministracion ", or has 12 , and replaces it with " AdministrationAccount ".

I try the following:

UPDATE computers SET departamento = REPLACE (departamento, (11), "Call Center 2");
UPDATE computers SET departamento = REPLACE (departamento, (12), "Call Center 3");

But it does not work: (

mysql sql

asked by EVM 22.11.2017 в 17:42

source

3 answers

How to call a stored procedure inside another How can I make an error-proof switch?

score 3 · Answer 1

I will leave the answer only as a reference ...

It is NOT RECOMMENDED TO MODERNIZE A TABLE (unless it is a data warehouse)

EYE This solution is only valid for MYSQL, in other engines it is written differently.

You should do something like this:

UPDATE
    computadoras c
    JOIN
    departamentoscomputadoras dc ON c.departamento=dc.id_departamento 
SET
    c.departamento = dc.departamento

score 0 · Answer 2

Instead of doing a replace you could try

UPDATE computers SET departamento = "Administración" WHERE departamento = 1;
UPDATE computers SET departamento = "Contabilidad" WHERE departamento = 2;

And in the second case, you would simply have to replace 1 and 2 by 11 and 12 and it will work perfectly for you:

UPDATE computers SET departamento = "Call Center 1" WHERE departamento = 11;
UPDATE computers SET departamento = "Call Center 2" WHERE departamento = 12;

score 0 · Answer 3

0

For what you want to do the correct query is this:

UPDATE computers SET departamento "Call Center 2" WHERE ID = 11;
UPDATE computers SET departamento "Call Center 3" WHERE ID = 12;
.....

answered by 22.11.2017 в 17:55