CODEWITHSUNDEEP
sqlmysqluploadsql-updatemixed
OrianFishken
OrianFishken

Reputation: 1

mysql columns values are mixed after upload

How do we update the table most effective way if 2 columns were uploaded incorrectly? After the upload I receive that some of the values from Name column moved to id column and vise versa. the result of the upload :

Name id Age
didi 6666666 30
miki 7777777 27
reg 5647899 32
Gimi 1234567 23
1234566 Hani 40

How do I change in the last row between id and name value effectively?

Tried to update with value from name to Id - then id is erased and vise versa. Thanks

Upvotes: -1

Views: 58

Answers (1)

Akina
Akina

Reputation: 42739

CREATE TABLE test (Name VARCHAR(64),    id VARCHAR(64), Age INT);
INSERT INTO test VALUES
('didi',    '6666666',  30),
('miki',    '7777777',  27),
('reg',     '5647899',  32),
('Gimi',    '1234567',  23),
('1234566', 'Hani',     40);
SELECT * FROM test;
Name id Age
didi 6666666 30
miki 7777777 27
reg 5647899 32
Gimi 1234567 23
1234566 Hani 40 
UPDATE test 
  SET id=(@id:=id), id=name, name=@id
  WHERE name NOT REGEXP '[^\\d]';
SELECT * FROM test;
Name id Age
didi 6666666 30
miki 7777777 27
reg 5647899 32
Gimi 1234567 23
Hani 1234566 40

fiddle

Upvotes: 1

Related Questions