Insert incorrect syntax near the keyword 'on'

Question

Trying to follow along this example: Insert into a MySQL table or update if exists

I made this query, but I keep getting:

Incorrect syntax near the keyword 'ON'.

What's wrong with this query?

INSERT INTO us_TEST (cell, [date], valueFlag, [value]) 
VALUES ('USW00024061', '2018-01-01', 'XXX', 299)
ON DUPLICATE KEY 
    UPDATE [value] = 299

Which of those 4 columns is the primary-key column? Are any of them primary-key columns? — Dai, Mar 09 '18 at 00:32
What's `MySQL`? As far as I know no... I think it's just `SQL`. How can I tell? — Rafael, Mar 09 '18 at 00:32
@R.M. What *actual* database system are you using? "SQL" is the basis for many actual languages, but there is no unified canonical SQL language that works on every database. — Dai, Mar 09 '18 at 00:32
`cell` and `date` are primary key columns. I don't know what the actual database system is, everyone around here calls it SQL. I'm doing this on Microsoft SQL SMS if that's any help... — Rafael, Mar 09 '18 at 00:38

Bill Karwin · Answer 1 · 2018-03-09T00:43:34.350

SQL is a standard language used by many database products, but every company who makes an SQL database product adds extra features to the SQL language beyond what's in the standard language.

Run the query SELECT @@version; This will tell you that you're using Microsoft SQL Server. Read examples and how to interpret what it tells you here: https://www.mssqltips.com/sqlservertip/2563/understanding-the-sql-server-select-version-command/

The INSERT ... ON DUPLICATE KEY UPDATE feature is specific to MySQL, which is one specific product that implements the SQL language.

Microsoft's product does not support INSERT ... ON DUPLICATE KEY UPDATE. But Microsoft might have something that uses different syntax and does what you need. See Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE

It's really annoying that Microsoft users keep calling their database product by the short nickname "SQL". It should properly be referred to as "Microsoft SQL Server" or maybe "SQL Server".

SQL is the name of an industry-standard programming language for database queries, not a specific brand or product name.

Calling the Microsoft database simply "SQL" is like using a text editor and calling it "Word". ;-)

score 2 · Accepted Answer · answered Mar 09 '18 at 00:43

2

You cannot do this in SQL Server. The syntax is for MySQL.

merge us_test as target using
      (values ('USW00024061', '2018-01-01', 'XXX', 299)
      ) as source(cell, [date], valueFlag, [value])
      on target.cell = source.cell  -- is this the right column?
when matched then
    update set [value] = source.[value]
when not matched then
    insert (cell, [date], valueFlag, [value]) values (cell, [date], valueFlag, [value]);

Actually, merge is pretty advanced functionality. You might as well stick with a basic update/insert.

answered Mar 09 '18 at 00:43

Gordon Linoff

1,242,037
58
646
786

This works perfectly for smaller inserts, however I'm running into issues if there are ~100k rows to be inserted/updated. How could I address this? – Rafael Apr 10 '18 at 18:51
1

@R.M. . . . I would suggest that you ask another question. 100k rows in a `values` clause seems cumbersome. – Gordon Linoff Apr 11 '18 at 03:05

Insert incorrect syntax near the keyword 'on'

2 Answers2