In TSQL I can state:
insert into myTable (ID) values (5)
GO
select * from myTable
In MySQL I can't write the same query.
What is the correct way to write this query in MySQL?
Asked
Active
Viewed 4.6k times
31
p.campbell
- 98,673
- 67
- 256
- 322
Ahmed Mozaly
- 1,454
- 2
- 15
- 22
-
1The "go" keyword in Sybase ASE and SQL Server is a batch delimiter. It signals the end of a batch. The semicolon implemented by both SQL Server and MySQL are statement delimiters (terminating an individual statement). To my knowledge, there is no batch delimiter for MySQL (no "GO" equivalent in MySQL). – Adam Jun 06 '19 at 11:51
5 Answers
52
Semicolon at the end of the statement.
INSERT INTO myTable (ID) values (5);
Jack
- 871
- 1
- 9
- 17
Jeff Fritz
- 9,821
- 7
- 42
- 52
-
4It doesn' even have to be at the end of line, just at the end of the statement – soulmerge Apr 22 '09 at 14:44
-
I created an insert statement with ID = 0, and then altered the field to auto increment. It was added as ID = 1.. – cosmoloc Mar 10 '15 at 12:09
17
The semicolon is the default delimiter. You can however redefine it to whatever you want with the DELIMITER keyword. From the MySQL manual:
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
This is not limited to stored procedure definitions of course.
Daniel Schneller
- 13,728
- 5
- 43
- 72
-
We can use multiple ways like delimiter // OR delimiter $$ when you want execute multiple stored proc in single go it's useful – Swapnil Thube Aug 23 '22 at 06:22
12
I think the problem is that GO is a batch terminator, not a statement terminator. After explicitly setting transactions, I got this code to execute without telling me that the procedure already exists. Without the transaction statements, I do get an error that the procedure already exists.
start transaction; drop procedure if exists usp_test; commit; start transaction; CREATE PROCEDURE usp_test() SELECT * from books; commit; call usp_test();
Brian
- 121
- 1
- 2
-
This is the correct answer. The Transaction blocks commit the queries within, effecting in MySql a similar "batch delimiter" effect to the "GO" batch delimiter in SQLServer. The two are not identical, but for the sake of getting a stored procedure defined, then calling it, this works well. – BRebey Mar 23 '21 at 20:22
10
Just a simple ;
so try
insert into myTable(ID) values (5);
select * from myTable;
Grant Collins
- 1,781
- 5
- 31
- 47
