1

I want to update column user with the last 6 characters replaced by id padded with zero. All keys under column user are in the format ^[A-Z]{2}[0-9]{8}$. For example, here's some sample data:

MariaDB [test]> SELECT * FROM users ORDER BY id ASC LIMIT 3;
+----+------------+
| id | user       |
+----+------------+
|  1 | AS12491264 |
|  2 | LQ10240183 |
|  3 | MR12037108 |
+----+------------+
3 rows in set (0.00 sec)

The expected result is:

+----+------------+
| id | user       |
+----+------------+
|  1 | AS12000001 |
|  2 | LQ10000002 |
|  3 | MR12000003 |
+----+------------+

I worked out the following two queries but both fail:

UPDATE users AS u
SET u.user = (
  SELECT
    CONCAT(
      SUBSTRING(s.user, 0, 4),
      LPAD(s.id, 6, '0')
    )
  FROM users AS s
  WHERE s.id = u.id
);

UPDATE users AS u
INNER JOIN (
  SELECT
    s.id AS id,
    CONCAT(
      SUBSTRING(s.user, 0, 4),
      LPAD(s.id, 6, '0')
    ) AS data
  FROM users AS s
) AS s ON s.id = u.id
SET u.user = s.data;

Both fail and give this result:

+----+--------+
| id | user   |
+----+--------+
|  1 | 000001 |
|  2 | 000002 |
|  3 | 000003 |
+----+--------+

Here's the DB schema (fetched from mysqldump):

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`)
) ENGINE=InnoDB AUTO_INCREMENT=8964 DEFAULT CHARSET=utf8;

I am running on MariaDB 10.1.38.

iBug
  • 35,554
  • 7
  • 89
  • 134
  • 2
    You are really over complicating that query – RiggsFolly Mar 14 '19 at 13:22
  • I strongly recommend testing expressions in a `SELECT` statement, before incorporating them into an `UPDATE`. – spencer7593 Mar 14 '19 at 13:27
  • @spencer7593 As its name suggests, it's really a test database. I have the original `mysqldump` to restore all changes in one shell command. – iBug Mar 14 '19 at 13:29
  • It doesn't matter if its a test database. My recommendation is the same. Expressions can be tested in a `SELECT` statement, and that allows us to return the current value as well as a new value, and that can be useful as a demonstration to a colleague, when we are asking for help. – spencer7593 Mar 14 '19 at 13:33
  • `SUBSTRING()` starts at 1, not 0. But see Nick's Answer. – Rick James Mar 18 '19 at 17:54

1 Answers1

4

This should do what you want:

UPDATE users
SET user = CONCAT(LEFT(user,4), LPAD(id, 6, '0'))

Output:

id  user
1   AS12000001
2   LQ10000002
3   MR12000003

Demo on dbfiddle

Update

Should the user column have a variable length, you can replace just the last 6 characters using this query, which uses CHAR_LENGTH to determine the length of the string and subtracts 6 from that to determine how many characters on the left to keep. We use CHAR_LENGTH instead of LENGTH so the query still works for multi-byte character encodings.

UPDATE users
SET user = CONCAT(LEFT(user,CHAR_LENGTH(user)-6), LPAD(id, 6, '0'))

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • This satisfies a specification of "first 4 characters kept", which is not (in the general case) the same thing as "last 6 characters replaced"; – spencer7593 Mar 14 '19 at 13:29
  • 2
    @spencer7593 `"All keys under column user are in the format ^[A-Z]{2}[0-9]{8}$"` which means they are 10 characters long, so keeping the first 4 is the same as replacing the last 6 – Nick Mar 14 '19 at 13:30
  • @spencer7593 have added an additional query which will replace *only* the last 6 characters and is independent of length (assuming it is at least 6 characters long). – Nick Mar 14 '19 at 13:52
  • I was fine with your solution, i was really just commenting about a more accurate description of the specification, in the more general case, when we didn't have the guarantee of a character length of 10. For the second query, the `LENGTH` function will work for most character sets, exceptions being encodings that use multiple bytes for [A-Z0-9] characters, such as `ucs2`, `utf16`, `utf32`. For those character sets, we would want to use the `CHAR_LENGTH` function (which operates on characters), rather than the `LENGTH` function (which operates on bytes). – spencer7593 Mar 14 '19 at 14:55
  • @spencer7593 no problem at all you made a valid point; the answer is better for it. And thanks for the `CHAR_LENGTH` comment too, I've updated the answer with that as well (and made the demo use a multi-byte encoding) – Nick Mar 14 '19 at 21:15