Mysql UNION keyword

Hello everyone,
The following code

USE skullcrashingwords;
DROP TABLE IF EXISTS abcd;
CREATE TABLE abcd(
ID INT AUTO_INCREMENT,
Name VARCHAR(255) not null collate utf8_unicode_ci,
KEY (ID)
);

INSERT INTO abcd(name)
SELECT ‘aaaaa’;

Creates table ‘abcd’ with one row containning ID number and “name” of ‘aaaaa’ value.
I try to add 2 lines to that table useing the following code:

USE skullcrashingwords;
DROP TABLE IF EXISTS abcd;
CREATE TABLE abcd(
  ID INT AUTO_INCREMENT, 
  Name VARCHAR(255) not null collate utf8_unicode_ci,
KEY (ID)
);

INSERT INTO abcd(name) 
SELECT 'aaaaa';

UNION
SELECT 'bbbb';

but here I get the following error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘UNION
SELECT ‘bbbb’’ at line 1
Can anyone explain what this error message means?
Thanks

This is a very strange syntax to insert a fix value into a table. Normally you should use

INSERT INTO abcd (name) VALUES (‘aaaaa’)

Your query is returning the aaaaa from the select statement as a constant value. So the union here makes no sense

What do you want to achieve?

I’m trying to insert multilple values to a table I’ve just created using union keyword. Multiple “INSERT INTO” did not work for me…

Right… a UNION is “I’m continuing; take what came before; attach this after.”

But you’ve told SQL “I’m done with this SQL statement.”

Where?

SELECT ‘aaaaa’; ← Right there.

Remove the semicolon.

Of course it works

INSERT INTO abcd (name) VALUES (‘aaaaa’), (‘bbbbb’)

Yeah, if you’re just inserting static values, the VALUES syntax is the normal one to use.
Strictly speaking there’s nothing wrong with using a INSERT INTO SELECT, it’s just… an awkwardly long winded and convoluted way of inserting static values. INSERT INTO SELECT is generally used for pulling data from one table into another.

1 Like

And it can lead you into big trouble if you change the columns of your table….

1 Like