CODEWITHSUNDEEP
phpsqlmysql
tmartin314
tmartin314

Reputation: 4171

SQL REPLACE not working

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?

Upvotes: 0

Views: 458

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85036

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}';

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332521

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...

Upvotes: 2

Related Questions