I have a small data table

I would like to update the columns c2, c3 and c4 for rows where the values of c1 have value c1 = replace (c1, 'b', '')

in a single request, but I'm not even sure if it's possible anymore

to have finally update test_Tb with values of row test_T, and test_Mb with values of row test_M

either my requests don't work, or it's too slow to complete ... i'm lost.

Thanks for your help.

CodePudding user response：

CREATE TABLE Data (
  c1 VARCHAR(255),
  c2 INT,
  c3 INT,
  c4 INT
);

INSERT INTO Data VALUES ('test_Tb', NULL, NULL, NULL);
INSERT INTO Data VALUES ('test_T', 1, 2, 3);
INSERT INTO Data VALUES ('test_C', 4, 5, 6);
INSERT INTO Data VALUES ('test_Mb', NULL, NULL, NULL);
INSERT INTO Data VALUES ('test_M', 7, 8, 9);

UPDATE Data AS target,
       ( SELECT * FROM Data WHERE c1 NOT LIKE '%b') AS source
   SET target.c2 = source.c2,
       target.c3 = source.c3,
       target.c4 = source.c4
 WHERE target.c1 = CONCAT(source.c1, 'b')

sqlfiddle