Insert and Update in statement error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert and Update in statement error

Author

Topic

CXXXV
Starting Member

12 Posts

Posted - 2009-07-18 : 11:57:52

I can't get this statement to run in SQLSERVER 2005

INSERT INTO Schedule (DATE, MEMBER_ID, QTY)
VALUES ('1/1/2009', 271, 99)
ON DUPLICATE KEY UPDATE QTY = 88

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-18 : 12:21:46

It's not a SQL Server 2005 syntax.
To mimic the functionality you need MERGE and SQL Server 2008.

IF EXISTS (SELECT * FROM Schedule WHERE ...)
UPDATE Schedule SET Qty = 88 WHERE ...
ELSE
INSERT Schedule VALUES ('1/1/2009', 271, 99)

N 56°04'39.26"
E 12°55'05.63"

CXXXV
Starting Member

12 Posts

Posted - 2009-07-18 : 14:14:07

This works well:

UPDATE Schedule SET QTY = 88
WHERE MEMBER_ID = 000 and DATE = '1/1/2009'
IF @@ROWCOUNT = 0
INSERT INTO Schedule (DATE, MEMBER_ID, QTY) VALUES ('1/1/2009',000,99)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-18 : 15:30:06

Great!

Let's just hope no-one happens to insert a record between your UPDATE and @@ROWCOUNT check.

What RDBMS is the query original from?

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-18 : 15:36:05

I think it is MySQL.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-18 : 15:45:03

After some Googling, I think you're right.
http://www.mysqlperformanceblog.com/2007/01/18/insert-on-duplicate-key-update-and-replace-into/

N 56°04'39.26"
E 12°55'05.63"

CXXXV
Starting Member

12 Posts

Posted - 2009-07-18 : 16:32:45

I am using MSSQL and wsa looking for an Update/Insert when I saw the DUPLICATE key word. Did not realize it was MySQL.

As far as someone entering a record that can't happen since it is all controlled from my application.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-18 : 16:46:27

And there is only one application running at any given time?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources