MySQL insert into select

Question

I'm trying to insert a row in to a table, that table has 3 foreign keys. To get to those keys I have "unique" filds to search.

sql

create table user(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(30) NOT NULL,
    access_token VARCHAR(32),
    reg_date TIMESTAMP,
    UNIQUE(username),
    UNIQUE(access_token)


);

create table pharmacy(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    places_id VARCHAR(50) NOT NULL,
    name VARCHAR(30) NOT NULL,
    coord VARCHAR(50) NOT NULL,
    description VARCHAR(100),
    reg_date TIMESTAMP,
    UNIQUE(places_id)
);

create table item(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    description VARCHAR(100),
    reg_date TIMESTAMP,
    UNIQUE(name)
);

create table item_bought(
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_user INT(6) UNSIGNED,
    id_pharmacy INT(6) UNSIGNED,
    id_item INT(6) UNSIGNED,
    price float(6,2) NOT NULL,
    bought_date date,
    reg_date TIMESTAMP,

    foreign key(id_user)    
       references user(id),

    foreign key(id_pharmacy)
       references pharmacy(id),

    foreign key(id_item)
       references item(id)
);

The query I'm building in JAVA:

INSERT INTO item_bought (id_user, id_pharmacy, id_item, price, bought_date)
     SELECT user.id, pharmacy.id, item.id, 12.32, date('1999-02-24') 
     FROM user, pharmacy, item
     WHERE ( SELECT user.id, user.username, pharmacy.id,
   pharmacy.places_id, item.id, item.name 
         FROM item, pharmacy, item 
         WHERE user.username='jhon', pharmacy.places_id='id1', item.name='ibuprufen' )

This is the error I'm getting:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Not unique table/alias: 'item'

Thanks for your help :D

Solution

Based on the answer of Gordon Linoff:

INSERT INTO item_bought (id_user, id_pharmacy, id_item, price, bought_date)
     SELECT (SELECT u.id FROM user u WHERE u.username = 'jhon'),
            (SELECT p.id FROM pharmacy p WHERE p.places_id = 'id1'),
            (SELECT i.id FROM item i WHERE i.name='ibuprufen'),
            (SELECT 13.22), (SELECT date('1999-02-12'));

Much more elegant!

What are you trying to do? It seems unlikely that you want a full cartesian products of users, pharmacies, and items inserted into the table. Sample data and desired results are very helpful, particularly when a query is so malformed. As a note: your specific syntax issue is the commas in the WHERE clause. — Gordon Linoff
– Gordon Linoff, Commented Jun 5, 2015 at 2:01

Gordon Linoff · Accepted Answer · 2015-06-05 02:08:28Z

1

I think the query you want is:

INSERT INTO item_bought (id_user, id_pharmacy, id_item, price, bought_date)
     SELECT (SELECT u.id FROM user u WHERE u.username = 'jhon'),
            (SELECT p.id FROM pharmacy p WHERE p.places_id = 'id1'),
            (SELECT i.id FROM item i WHERE i.name='ibuprufen');

Your query has numerous errors -- the same table multiple times in the from with no table aliases, the commas in the where clause, a select in a scalar context that returns multiple columns and (possibly) multiple rows rather than one. In addition, using commas in the where clause is very bad practice.

answered Jun 5, 2015 at 2:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CiberWizZ Over a year ago

Thanks, very elegant solution, just had to add 2 more selects with the price and bought_date. I actually searched a lot to find a good solution to this, never found annything like this. Thanks oh great Knight of the StackOverflow! :D

user2684182 · Accepted Answer · 2015-06-05 02:02:34Z

1

The problem seems to be this line:

FROM item, pharmacy, item

You have item twice. Try removing one.

answered Jun 5, 2015 at 2:02

user2684182

Comments

Francisco Romero · Accepted Answer · 2015-06-05 02:04:14Z

1

In your code you are calling two times to the same table in the same query. It is wrong. It should be:

INSERT INTO item_bought (id_user, id_pharmacy, id_item, price, bought_date)
     SELECT user.id, pharmacy.id, item.id, 12.32, date('1999-02-24') 
     FROM user, pharmacy, item
     WHERE ( SELECT user.id, user.username, pharmacy.id,
   pharmacy.places_id, item.id, item.name 
         FROM item, pharmacy
         WHERE user.username='jhon', pharmacy.places_id='id1', item.name='ibuprufen' )

answered Jun 5, 2015 at 2:04

Francisco Romero

13.3k23 gold badges100 silver badges173 bronze badges

Collectives™ on Stack Overflow

MySQL insert into select

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related