SQLite: insert with select

Question

Can anyone tell me why the following SQL refuses to insert more than 1 result (using sqlite3)? And how to make it insert all distinct values from the select port_shipment?
The cargo table in this database contains more than 2000 distinct values for port_shipment, but I tried several versions of the insert-command which resulted always in only 1 value inserted.

create table port (
    port_ID integer  not null  primary key,
    port_description text unique collate nocase,
    port_name text,
    country text,
    lat text,
    lon text
)

insert or ignore into port (port_ID, port_description)
values (null, (SELECT port_shipment FROM cargo))

Thanks!

Gordon Linoff · Accepted Answer · 2013-06-12 20:24:57Z

2

The values statement is not needed with select. Try this:

insert or ignore into port (port_ID, port_description) 
    SELECT NULL, port_shipment
    FROM cargo

answered Jun 12, 2013 at 20:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQLite: insert with select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related