Insert if not exists else update mysql

Hello.  I have been looking for tihs everywhere.  im trying to run this query

IF NOT EXISTS (SELECT * FROM tutorials WHERE tutorialid = 'Alg1_01_02_0001' AND subjectid='1') THEN
	INSERT INTO tutorials (tutorialid, keywordlist, subjectid) VALUES ('Alg1_01_02_0001', 'Hello World', '1')
ELSE
	UPDATE tutorials SET keywordlist = "HELLO WORLD" WHERE tutorialid = 'Alg1_01_02_0001' AND subjectid = '1'

Open in new window


this is giving me the error

Error Code : 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 'IF NOT EXISTS (SELECT * FROM tutorials WHERE tutorialid = 'Alg1_01_02_0001' AND ' at line 1

and i cant find out what is wrong with the syntax.

For reasons i dont want to go into i can not use anything that uses duplicate key to accomplish this or the REPLACE

Thank You!
LVL 3
brettererAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
ok, sorry.

well only other thing is perhaps a stored procedure within mysql or use some other app to run mysql commands eg vb/ado

mysql stored procedure - something along these lines, cant remember exact syntax
try maybe use a variable then check that (eg cnt)


create procedure myproc
begin
    SELECT count(*) into cnt FROM tutorials WHERE tutorialid = 'Alg1_01_02_0001' AND subjectid='1'
    if cnt = 0 then
        INSERT INTO tutorials (tutorialid, keywordlist, subjectid) VALUES ('Alg1_01_02_0001', 'Hello World', '1')
    else
        UPDATE tutorials SET keywordlist = "HELLO WORLD" WHERE tutorialid = 'Alg1_01_02_0001' AND subjectid = '1'
    end if
end
0
 
rockiroadsCommented:
MySql has something called on duplicate which you can use

basically insert the record and if duplicate key error occurs then perform the update

INSERT INTO tutorials (tutorialid, keywordlist, subjectid) VALUES ('Alg1_01_02_0001', 'Hello World', '1')
ON DUPLICATE KEY UPDATE SET keywordlist = "HELLO WORLD"

Its something along those lines anyways. your dup key index would need to be setup correctly I guess

backup table/db just in case it didnt quite go according to plan
0
 
brettererAuthor Commented:
rockiroads,  Thankyou for your quick response but let me point out what i said before

and i quote
" i can not use anything that uses duplicate key to accomplish this"
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
brettererAuthor Commented:
Error Code : 1327
Undeclared variable: cnt
(0 ms taken)
0
 
rockiroadsCommented:
can u try defining the variable, after begin, before select

DECLARE cnt INT DEFAULT 0
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Try this.

REPLACE INTO `tutorials`
SET `tutorialid` = 'Alg1_01_02_0001',
`keywordlist` = 'Hello World',
`subjectid` = '1';

got from
http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html
0
 
brettererAuthor Commented:
Brichsoft.  That did do it although i said i didnt want to use replace.   becuase of database structure.  I did modify database and it worked.  
0
 
brettererAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.