SQLite Forum

How to insert duplicate rows?

How to insert duplicate rows?

(1) By Ethan (dalishi) on 2021-09-09 05:42:31 [link]

I have a simple table without specifying primary key or unique key. I noticed that I was not able to insert duplicate records. Is there a way to insert duplicate row? I heard about the rowid may do the tricks for me but Im not sure how to use it.

"CREATE TABLE Waypoints("    \
               "id TEXT, "    \
               "type INT, "    \
               "xcoord REAL, "    \
               "ycoord REAL, "    \
               "ref_yaw REAL, "     \
               "ref_cur REAL, "
               "lane_width REAL, "    \
               "is_stop INT, "    \
               "is_junct INT, "    \
               "is_carpark INT, "    \
               "is_zebra_crossing INT, "    \
               "is_round_about INT, "    \
               "is_traffic_light INT, "    \
               "is_traffic_sign INT, "    \
               "behavior TEXT, "      \
               "lane_id TEXT);";

(2) By John Dennis (jdennis) on 2021-09-09 06:35:39 in reply to 1 [link]

Why all the double quotes? They seem to be in very strange places. 

Duplicate rows certainly work:

create table dups (a text,b int);
insert into dups values ('aaa',1);
insert into dups values ('aaa',1);
insert into dups values ('aaa',1);
select * from dups;

(3) By Stephan Beal (stephan) on 2021-09-09 06:38:44 in reply to 2 [link]

> Why all the double quotes? They seem to be in very strange places. 

Formatting quirk. Tap the "source" link on the top post to see the original formatting.

(7) By John Dennis (jdennis) on 2021-09-09 09:01:28 in reply to 3

Thanks. This line is missing the backslash at the end

"ref_cur REAL, "

Whatever affect that might have. Obviously the table gets created, so this is not the problem with the inability to insert duplicate rows.

(9) By Scott Robison (casaderobison) on 2021-09-09 16:20:31 in reply to 7 [link]

Assuming C or C++, it has no effect. Those backslashes are superfluous as given, as the languages mandate that adjacent string literals are merged to create a single longer string literal.

UNLESS that is a copy and paste from a macro definition, in which case the slashes would be important.

So ... as others have observed, too much missing context.

(4) By Gunter Hick (gunter_hick) on 2021-09-09 06:51:53 in reply to 1 [link]

Can you replicate the issue using the SQLite shell alone? If that is the case, then please post the exact SQL used.

If not, then there is probably something wrong in what you are not showing. Like which release of SQLite you are working with, the programming language used (maybe C or similar from the obvious expectation that white space separated literal strings are concatenated and the extraneous backslash line continuation characters?), and the actual code.

(5) By Simon Slavin (slavin) on 2021-09-09 07:59:42 in reply to 1 [link]

Your table definition has incorrect syntax.  Doing it that way gives you a column called <code>id TEXT</code> which has no affinity.  The double quotes should not be there at all:

<code>id TEXT</code>

If you must use double quotes for your column names then they should be around the column name, not the affinity:

<code>"id" TEXT</code>

and you should use them everywhere you refer to a column name.

(6) By Gunter Hick (gunter_hick) on 2021-09-09 09:00:21 in reply to 5 [link]

What you are referring to is a formatting quirk. The OP is building the CREATE TABLE statement by concatenating string literals on continuation lines. The double quotes do not appear in the statement itself.

"fragment" ...

(8) By Simon Slavin (slavin) on 2021-09-09 13:47:55 in reply to 6 [link]

Thanks for the headsup.  I saw a comment about the formatting above but didn't understand it.  You explained it.

Okay, so the question isn't what I thought it was.  Please ignore my previous post.  Here's what I should have responded.

There's no reason why duplicate INSERTs shouldn't work.  What happens when you try it ?