Using ON DUPLICATE KEY UPDATE with two tables

user_14705308 0
Viewed 304

I am trying to use On Duplicate Key Update.

My structure is I want to ADD new data and UPDATE table (in my student table with is imported by Excel) to my existing data. Some data in my table have existing emails and I want to just update the other columns. (with new values and those with null values are ignored in the new tables).

My email is an Unique foreign key and the rest of the data are bind. The code doesn't work and it kept prompting ON has a syntax error.

CREATE TABLE Students
(   email                   VARCHAR(50) NOT NULL    FOREIGN KEY REFERENCES  Users(email),
a varchar(50) null,
b varchar(50) null,
c varchar(50) null,
c varchar(50) null)


INSERT INTO [dbo].[Students](email, a, b, c, d)
select t2.email, t2.a, t2.b, t2.c, t2.d
from [dbo].[2020students$]
ON DUPLICATE KEY UPDATE a = value(if(t2.a IS NOT NULL, a,t2.a)), a = value(t2.b), a = value(t2.c), a = value(t2.d)
mysql-error-1064 sql-insert sql-update mysql sql
1 Answers

I think the syntax you want is:

insert into students(email, a, b, c, d)
select email, a, b, c, d
from `2020students$`
on duplicate key update 
    a = coalesce(values(a), a), 
    b = values(b), 
    c = values(c), 
    d = values(d)

This resolves the conflicts by updating values b, c and d. Special case is taken of a, that is updated only if the new value is not null.

This assumes that you are running MySQL - which is not obvious after all, because the use of square brackets and schema dbo look more like SQL Server syntax.

user_106767160
Related