How do I update a table using a staging table to avoid inserting duplicate records?

IamWarmduscher

I have a main table called arrests with primary key arrest_key that is currently empty. I have a staging table called arrests_temp that contains one month of data. The staging table looks like this: https://i.imgur.com/v8QGynD.png

I have the following so far:

UPDATE arrests 
SET arrests.arrest_key = arrests_temp.arrest_key 
FROM arrests LEFT JOIN arrests_temp 
ON arrests.arrest_key = arrests_temp.arrest_key 
WHERE arrests.arrest_key != arrests_temp.arrest_key;
    
INSERT INTO arrests (arrest_key, arrest_date, pd_cd, pd_desc, ky_cd) 
SELECT arrest_key, arrest_date, pd_cd, pd_desc, ky_cd
FROM arrests_temp 
WHERE arrest_key NOT IN (SELECT arrest_key FROM arrests);

I keep getting the following error:

ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM arrests LEFT JOIN arrests_temp \n ON arrests.arrest_key = arrests_temp.ar' at line 3")

[SQL: 
    UPDATE arrests 
    SET arrests.arrest_key = arrests_temp.arrest_key 
    FROM arrests LEFT JOIN arrests_temp 
    ON arrests.arrest_key = arrests_temp.arrest_key 
    WHERE arrests.arrest_key != arrests_temp.arrest_key;
    ]
(Background on this error at: http://sqlalche.me/e/13/f405)

What am I doing wrong?

juergen d

You were using the SQL Server syntax but MySQL works differently

UPDATE arrests 
LEFT JOIN arrests_temp ON arrests.arrest_key = arrests_temp.arrest_key 
SET arrests.arrest_key = arrests_temp.arrest_key 
WHERE arrests.arrest_key != arrests_temp.arrest_key

Every DB engine has a somewhat different SQL syntax.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How do I update a table that references duplicate records?

How do I best avoid inserting duplicate records in CakePHP?

How to find duplicate between staging and main table with millions records

How do I avoid loading all child records with the actual table

How to avoid duplicate record while inserting data using user defined table type in SQL Server

How to insert records from staging table to master table and update records as sync status true

How do I update MSSQL table values from the contents of a second table using the ID from a third table to match the records?

MYSQL: How to avoid inserting duplicate records?

Mongoose: How to avoid inserting duplicate records?

Postgres 9.4 -- How do I ignore duplicate key when inserting rows into a table

Avoid Duplicate Records with BeforeChange Table Event

Update a table after inserting records with an Inner Join

How to update records in table?

How to duplicate records in same table

Inserting records into a MySQL table using Java

Inserting duplicate entries into the target table using MERGE

How do I get a records for a table that do not exist in another table?

Update a column of same table if there is duplicate records

How to update records using select records of the same table

How do I duplicate a column in a SQL Table?

How to do a hibernate saving without duplicate records in a different table

SQL - How to avoid inserting duplicate rows from another table while matching multiple columns

How can I delete duplicate join table records in MySQL?

How can i create duplicate records based the value in another table

How do I write an Update SQL to copy multiple records from one table to update the corresponding field on another table?

how to avoid the inconsistency of data while inserting into table

How do I update a table with a form in access using VBA or a Macro?

How I can avoid duplicate records for insert

How do I insert a "remove button" to every table row when inserting a new row using javascript/jQuery