×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

mysql cannot add rows with "UNION"

thread436-1820606

mysql cannot add rows with "UNION"

mysql cannot add rows with "UNION"

(OP)
Hello everyone,
Here is code to create table named 'abcd'/

CODE --> mysql

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 --> mysql

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:
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 help me with that enigmatic error ?
Thanks

RE: mysql cannot add rows with "UNION"

It's one statement not two, so remove the first semicolon and try

CODE

INSERT INTO abcd(name) 
SELECT 'aaaaa'
UNION ALL
SELECT 'bbbb'
;

RE: mysql cannot add rows with "UNION"

(OP)
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...

RE: mysql cannot add rows with "UNION"

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

RE: mysql cannot add rows with "UNION"

(OP)
How do I specify collation ?

RE: mysql cannot add rows with "UNION"

Just look into the documentation:
https://dev.mysql.com/doc/refman/8.0/en/charset-li...

So if you write

CODE

_utf8'aaaaa' collate utf8_unicode_ci
you make it clear this is the same collation as your name column. Just 'aaaaa' is interpreted as whatever your connection or default collation is.

Chriss

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close