Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

mysql cannot add rows with "UNION" 1

Status
Not open for further replies.

lupidol

Programmer
Apr 23, 2008
125
0
0
IL
Hello everyone,
Here is code to create table named 'abcd'/
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';
When I run this code a new abcd table is created containing one row.
When I try to create that table with 2 lines I run 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 recieve the following error message:
[pre]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[/pre]
Can anyone help me with that enigmatic error ?
Thanks
 
It's one statement not two, so remove the first semicolon and try
Code:
INSERT INTO abcd(name) 
SELECT 'aaaaa'
UNION ALL
SELECT 'bbbb'
;
 
Thanks,
unnecessary semicooln was illcopied, sorry for that.
Even though your code yielded error message it works !
I use "UNION" alone on tables that have INDEXES attached and it works ! Table with "KEY" column and with no accompanied INDEX "UNION" alone doesn't work but your suggestion made it work !
Thanks, but I still wonder why...
 
For union the types of all columns have to match the target columns. And since you specify a collation in the name column, but only use string literals without a specified collation, that might already cause a warning about possible loss of data in conversion. Key columns pose another problem, but that concerns the insert part, not the union of records that finally are inserted.

Chriss
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top