
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Updating a MySQL column that contains dot (.) in its name?
If the MySQL column contains dot (.) in its name, then you need to use backticks around the column name. To understand the above concept, let us create a table. The query to create a table is as follows
mysql> create table UpdateDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `User.FirstName.LastName` varchar(60) -> ); Query OK, 0 rows affected (0.54 sec)
Insert some records in the table using insert command.
The query is as follows
mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('John Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('Adam Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('Carol Taylor'); Query OK, 1 row affected (0.25 sec) mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('Mitchell Johnson'); Query OK, 1 row affected (0.19 sec) mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('David Brown'); Query OK, 1 row affected (0.16 sec) mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('Larry Miller'); Query OK, 1 row affected (0.22 sec)
Display all records from the table using select statement.
The query is as follows
mysql> select *from UpdateDemo;
The following is the output
+--------+-------------------------+ | UserId | User.FirstName.LastName | +--------+-------------------------+ | 1 | John Smith | | 2 | Adam Smith | | 3 | Carol Taylor | | 4 | Mitchell Johnson | | 5 | David Brown | | 6 | Larry Miller | +--------+-------------------------+ 6 rows in set (0.00 sec)
Use backticks around the column name User.FirstName.LastName which contains (.).
The query is as follows
mysql> update UpdateDemo set `User.FirstName.LastName`='David Miller' where UserId=5; Query OK, 1 row affected (0.19 sec) Rows matched: 1 Changed: 1 Warnings: 0
Let us check the table record once again. The row with UserId=5 is successfully updated.
The query is as follows
mysql> select *from UpdateDemo;
The following is the output
+--------+-------------------------+ | UserId | User.FirstName.LastName | +--------+-------------------------+ | 1 | John Smith | | 2 | Adam Smith | | 3 | Carol Taylor | | 4 | Mitchell Johnson | | 5 | David Miller | | 6 | Larry Miller | +--------+-------------------------+ 6 rows in set (0.00 sec)
- Related Articles
- Updating only a single column value in MySQL?
- Can we select field name in MySQL that contains an asterisk?
- Given a column name how can I find which tables in a MySQL database contain that column?
- Updating a MySQL table row column by appending a value from user defined variable?
- Rename column name in MySQL?
- Name the liquid that contains sperms.
- Updating a record in MySQL using NodeJS
- Fetch a specific column value (name) in MySQL
- Updating boolean value in MySQL?
- A single MySQL query to update only specific records in a range without updating the entire column
- Wildcards in column name for MySQL?
- Display distinct column name in MySQL
- Find the column name that contains value greater than a desired value in each row of an R data frame.
- Updating last entry of a particular ID in MySQL
- How to select a column name with spaces in MySQL?

Advertisements