mysql columns values are mixed after upload

Question

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

I assum e you are loading from something like a CSV file? First guess is that one column does not have a Name and therefore everything after that gets shifted left by one column. — RiggsFolly, Jun 08 '23 at 10:03
Although this looks like you may have one row without a Name or an Age. Check your input data. — RiggsFolly, Jun 08 '23 at 10:04
Also it would be useful to see how you are loading this and an example of the input a well — RiggsFolly, Jun 08 '23 at 10:10
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jun 08 '23 at 10:52

score 1 · Answer 1 · answered Jun 08 '23 at 10:11

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

mysql columns values are mixed after upload

1 Answers1