CODEWITHSUNDEEP
mysqlsql-updatemariadbinner-join
iBug
iBug

Reputation: 37227

MySQL Update a column with values combined from target column and another column

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.

Upvotes: 1

Views: 930

Answers (1)

Nick
Nick

Reputation: 147166

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

Upvotes: 4

Related Questions