How to execute SQL queries skipping error?

Question

I try to import to table data from sql files using command line. This data contents duplicates in filed url.

But field url in table is unique. So when I try to insert data I get error "Dublicate entry"

How to inmport all data skipping this error?

You should probably import to a new table, and insert only unique records. — HoneyBadger
– HoneyBadger, Commented Dec 6, 2016 at 13:15
How to do this? Create one table and how insert only unique rows? — MisterPi
– MisterPi, Commented Dec 6, 2016 at 13:16
You should create query from the table you have created with DISTINCT Or GROUP BY if is possible — Weenesta - Mathieu Dormeval
– Weenesta - Mathieu Dormeval, Commented Dec 6, 2016 at 13:19

vp_arth · Accepted Answer · 2016-12-06 13:18:36Z

6

You can to use the --force (-f) flag.

mysql -u userName -p -f -D dbName < script.sql

From man mysql:

· --force, -f

Continue even if an SQL error occurs.

answered Dec 6, 2016 at 13:18

vp_arth

15k4 gold badges43 silver badges72 bronze badges

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

Comments

David דודו Markovitz · Accepted Answer · 2016-12-06 13:24:29Z

2

Create a staging table with the same structure as your destination table but without the constraints (unique index included).
Manually check the duplicates and decide on the way you want to choose between duplicates rows / merge rows.
Write the appropriate query and use "insert into ... select ...".

answered Dec 6, 2016 at 13:24

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Comments

Rahul · Accepted Answer · 2016-12-06 13:16:15Z

0

How to inmport all data skipping this error?

Drop the index for time being -> run your batch insert -> recreate the index back

answered Dec 6, 2016 at 13:16

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

vp_arth Over a year ago

recreate the index back will be failed

Gordon Linoff · Accepted Answer · 2016-12-06 13:19:13Z

0

If you are using insert, the you can ignore errors using ignore error or on duplicate key update (preferable because it only ignores duplicate key errors).

If you are using load data infile, then you can use the ignore key word. As described in the documentation:

If you specify IGNORE, rows that duplicate an existing row on a unique key value are discarded. For more information, see Comparison of the IGNORE Keyword and Strict SQL Mode.

Or, do as I would normally do:

Load the data into a staging table.
Validate the staging table and only load the appropriate data into the final table.

answered Dec 6, 2016 at 13:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to execute SQL queries skipping error?

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related