Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 382 of 547
MySQL throws an error when the table name "match" is not surrounded by single quotes?
Do not use single quotes. You need to use backticks around the table name match, since it is a reserved name in MySQL. Following is the error that occurs :mysql> select *from match; ERROR 1064 (42000) : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match' at line 1Let us first create a table and fix the occurrence of the above error using backticks around the reserved word match, used here as table name −mysql> create table `match` ( Id int NOT ...
Read MoreFix MySQL ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
To fix this error, you need to add PRIMARY KEY to auto_increment field. Let us now see how this error occurs −Here, we are creating a table and it gives the same error −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT, StudentName varchar(40), StudentAge int ); ERROR 1075 (42000) : Incorrect table definition; there can be only one auto column and it must be defined as a keyTo solve the above error, you need to add PRIMARY KEY with AUTO_INCREMENT. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT ...
Read MoreGet Nth weekday of the month from a column with DATE records in MySQL
We need to find the weekday i.e. week 1 from date 1 to 7, week 2 from date 8 to 14, etc. To get the day, use DAY() function in MySQL. Set the conditions to get the weekday (number) using CASE statement.Let us now see an example and create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-09-12'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('2019-09-06'); Query OK, 1 row affected (0.16 sec) mysql> ...
Read MoreHow can I select every alternative row and display in descending order in SQL?
To fetch every alternative row, use MOD() under WHERE. Then use ORDER BY DESC to display the result in descending order −select *from yourTableName where mod(yourColumnName, 2)=1 order by yourColumnName DESC;Let us first create a table −mysql> create table DemoTable ( UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(40), ClientAge int ); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ClientName, ClientAge) values('Chris', 34); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable(ClientName, ClientAge) values('Tom', 45); Query OK, 1 row affected (0.19 sec) mysql> ...
Read MoreSelect all email addresses beginning with 5 numeric characters (regular expression) in MySQL
To get the email addresses beginning with 5 numeric characters, the optional solution is to use REGEXP −select *from yourTableName where yourColumnName regexp "^[0-9]{5}";Let us first create a table −mysql> create table DemoTable ( UserEmailAddress varchar(100) ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('6574John@gmail.com'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Carol23456@gmail.com'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('98989Chris_45678@gmail.com'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Mike12@gmail.com'); Query OK, 1 row affected (0.43 ...
Read MoreGet specific value of cell in MySQL
Let us first create a table −mysql> create table DemoTable ( Name varchar(40), Score int ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris Brown', 78); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('John Doe', 88); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Carol Taylor', 98); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David Miller', 80); Query OK, 1 row affected (0.68 sec)Display all records from the table using select statement −mysql> select *from ...
Read MoreUsing the value of an alias inside the same MySQL SELECT statement
You cannot directly use an alias in the SELECT. Instead, use a user-defined variable. Following is the syntax. Here, @yourAliasName is our variable and alias −select @yourAliasName :=curdate() as anyAliasName, concat(‘yourValue.', yourColumnName, ' yourValue', @yourAliasName) as anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable ( Name varchar(40) ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John Smith'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Chris Brown'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('David Miller'); ...
Read MoreAdd some value to an int type column value in a table without knowing its current value in SQL?
For this, simply use UPDATE command along with SET. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentScore int ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentScore) values(78); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable(StudentScore) values(89); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(StudentScore) values(67); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(StudentScore) values(95); Query OK, 1 row affected (0.25 sec)Display all records from the table ...
Read MoreMySQL query to check if multiple rows exist?
Let us first create a table −mysql> create table DemoTable1219 ( Id int, Name varchar(40) ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1219 values(100, 'Adam'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1219 values(101, 'John'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1219 values(102, 'Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1219 values(103, 'Bob'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable1219;This will produce the ...
Read MoreIncrement multiple Timestamp values by setting the incremented value in a user-defined variable in SQL
The incremented value can be set in a user-defined variable as shown below. Here, “yourValue” is the incremented value. After that, use MySQL UPDATE to update the column and increment timestamp values −set @anyVariableName :=yourValue; update yourTableName set yourColumnName=yourColumnName+interval (@yourVariableName) second;Let us first create a table −mysql> create table DemoTable ( DueDatetime timestamp ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-31 12 :30 :40'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('2019-09-06 10 :00 :00'); Query OK, 1 row affected (0.73 sec) ...
Read More