Syntax error Query with values prepended by ampersand works in Oracle but not in MySQL?

Query with values prepended by ampersand works in Oracle but not in MySQL?

Ampersands work in Oracle. To work it in MySQL, use @ as shown in the following syntax −

SET @yourVariableName1 = yourValue, @yourVariableName2 = yourValue, @yourVariableName3 =yourValue,.........N;
insert into yourTableName values(@yourVariableName1,@yourVariableName2,@yourVariableName3,........N);

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

mysql> create table Student_Information
   -> (
   -> StudentId int,
   -> StudentName varchar(100),
   -> StudentAge int,
   -> StudentMarks int,
   -> StudentCountryName varchar(10)
   -> );
Query OK, 0 rows affected (0.75 sec)

Here is the query with values prepended by @. Insert some records in the table using insert command. −

mysql> SET @Id = 10001, @Name = 'Carol', @Age =23 ,@Marks =89, @CountryName = 'US';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into Student_Information values(@Id, @Name, @Age ,@Marks, @CountryName);
Query OK, 1 row affected (0.19 sec)

Now you can display all records from the table using select statement. The query is as follows −

mysql> select *from Student_Information;

Here is the output −

+-----------+-------------+------------+--------------+--------------------+
| StudentId | StudentName | StudentAge | StudentMarks | StudentCountryName |
+-----------+-------------+------------+--------------+--------------------+
| 10001     | Carol       | 23         | 89           | US                 |
+-----------+-------------+------------+--------------+--------------------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

122 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements