Using single quotes around database and table name isn’t working in MySQL?

You need to use backticks around table name as well as database name. The syntax is as follows:

UPDATE `yourDatabaseName`.`yourTableName`
SET yourColumnName1=yourColumnName1+1
WHERE yourColumnName2=’yourValue’;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> use test;
Database changed
mysql> create table Add1Demo
   -> (
   -> Id varchar(10),
   -> Value int
   -> );
Query OK, 0 rows affected (1.19 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into Add1Demo values('1',780);
Query OK, 1 row affected (0.17 sec)
mysql> insert into Add1Demo values('2',1001);
Query OK, 1 row affected (0.18 sec)
mysql> insert into Add1Demo values('3',1654);
Query OK, 1 row affected (0.15 sec)
mysql> insert into Add1Demo values('4',3999);
Query OK, 1 row affected (0.09 sec)
mysql> insert into Add1Demo values('5',5999);
Query OK, 1 row affected (0.10 sec)
mysql> insert into Add1Demo values('6',799);
Query OK, 1 row affected (0.08 sec)
mysql> insert into Add1Demo values('8',899);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from Add1Demo;

The following is the output:

+------+-------+
| Id   | Value |
+------+-------+
| 1    |   780 |
| 2    |  1001 |
| 3    |  1654 |
| 4    |  3999 |
| 5    |  5999 |
| 6    |   799 |
| 8    |   899 |
+------+-------+
7 rows in set (0.00 sec)

Here is the query to use backticks around database name and table name. Do not use single quotes for database and table name. The query is as follows, let’s say to add 1 to current field.

mysql> update `test`.`Add1Demo`
   -> set Value=Value+1
   -> where Id='6';
Query OK, 1 row affected (0.17 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now you can check the table record once again with specific id 6. The query is as follows:

mysql> select *from Add1Demo where Id='6';

The following is the output:

+------+-------+
| Id   | Value |
+------+-------+
| 6    |   800 |
+------+-------+
1 row in set (0.00 sec)

Chandu yadav

Published on 27-Feb-2019 05:34:38

Previous Page Print Page