MERGE语句上的语法错误 [英] Syntax error on MERGE statement

问题描述

我正在尝试在Go中执行MERGE语句:

I'm trying to do a MERGE statement in Go:

query := "MERGE staged ON (email=$1)" +
" WHEN NOT MATCHED THEN INSERT (email, secret, passwd, ts, newAcct)" +
" VALUES($1,$2,$3,$4,TRUE)" +
" WHEN MATCHED THEN UPDATE staged SET" +
" newAcct=TRUE, existingUser=NULL, secret=$2, ts=$4"

_, err := db.Exec(query, email, secret, passwd, time.Now())

但是我得到了这个错误:

but I got this error:

pq: S:"ERROR" F:"scan.l" R:"scanner_yyerror" L:"1001" M:"syntax error at or near \"MERGE\"" P:"1" C:"42601"

在MySQL中也是如此:

The same happens in MySQL:

Error 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 'MERGE staged ON (email=?) WHEN NOT MATCHED THEN INSERT (email, secret, passwd, t' at line 1

怎么了?

推荐答案

MERGE，等效于

插入...在重复的键更新上

INSERT ... ON DUPLICATE KEY UPDATE

尝试一下

INSERT INTO tableName (email, secret, passwd, ts, newAcct) 
VALUES ($1,$2,$3,$4,TRUE)
ON DUPLICATE KEY UPDATE newAcct=TRUE, existingUser=NULL, secret=$2, ts=$4

，但是请确保将email设置为Primary Key或Unique.

but be sure email is set as Primary Key or Unique.

这篇关于MERGE语句上的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！