CODEWITHSUNDEEP
sqldatabasesqliteinsert
Abdalrahman Shatou
Abdalrahman Shatou

Reputation: 4748

SQL - Insert rows from one table to another when a condition is satisfied

What's wrong in this code:

INSERT INTO main.table_1 
   (SELECT * 
    FROM TempDatabase.table_1 
    WHERE TempDatabase.table_1.ID != "5855CA0F8E23")

I made a condition on ID to prevent duplicates (and also because I applied a constrain on main.table_1). However, as I run in SQLiteManager, I got a syntax error "near SELECT".

If I removed the brackets, still I got another error due to violating constrain on main.table_1 (as If the WHERE condition is totally ignored!)

What am I doing wrong over here!

Upvotes: 0

Views: 3785

Answers (2)

Elliot Chance
Elliot Chance

Reputation: 5736

You need to use IGNORE or REPLACE (http://www.sqlite.org/lang_insert.html)

If a constraint violation occurs the rows will be skipped, and it will continue:

INSERT OR IGNORE INTO main.table_1 SELECT * from TempDatabase.table_1

If a constraint violation occurs the record in main.table_1 will be deleted and the full row from TempDatabase.table_1 will be inserted:

INSERT OR REPLACE INTO main.table_1 SELECT * from TempDatabase.table_1

There is no method to "merge rows" - that is to aggregate data from both tabs into new rows.

Upvotes: 2

user684934
user684934

Reputation:

The correct syntax is your second try, without the parentheses. (Parentheses are used to define the column names that you will insert into.)

You are getting a constraint violation because, despite what you think, inserting your temp data into your main database is actually violating some constraint on your main db table, even if you exclude the one record that has the id 5855CA0F8E23. Check your constraints.

Upvotes: 3

Related Questions