SQL REPLACE not working

Question

There are only two columns and I want to UPDATE only if menu_id exists. Otherwise I want to insert a new row. Here's my query:

REPLACE `dol_subs_menu_access` 
SET `menu_id` = '{$aVars['menu_item']}',
`mlevels` = '{$sMemLevels}'

This creates a new row everytime. Is there something I'm missing?

Answer 1

You may want to look at mySQL's ON DUPLICATE KEY syntax. This will allow you to insert a record or update a record if it already exists. You will need to include the primary key in your query though.

here is an example query from their documentation page:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

I think it would look something like this for your query (test it first though)

INSERT INTO `dol_subs_menu_access` (menu_id,mlevels) 
       VALUES ('{$aVars['menu_item']}','{$sMemLevels}')
ON DUPLICATE KEY UPDATE mlevelsMemLevels='{$sMemLevels}';

Answer 2

Are either of the columns in your statement the primary key? My guess is they aren't, which is what the REPLACE statement (not to be confused with the string replacement function) needs to determine if it is replacing vs inserting...