sql - Using IF statement in MySQL to update or insert without Stored Procedure? -

is somehow possible use if statement in mysql either update or insert record table, without having within stored procedure ?

given nature of setup, cannot use stored procedures @ time, why ask ..

basically i'm trying do:

if not exists (select ........)     insert ...... else     update ...... end if 

here code:

if not exists (     select * `oc_product_attribute`     product_id = (select product_id `oc_product_description` name = 'product_name_here')     , attribute_id = (select attribute_id `oc_attribute_description` name='attribute_name_here') )     insert `oc_product_attribute`     values     (     select product_id `oc_product_description` name = 'product_name_here',     select attribute_id `oc_attribute_description` name='attribute_name_here',     1,     'xyz'     ) else     update `oc_product_attribute`     set text = 'abc'     product_id = (select product_id `oc_product_description` name = 'product_name_here')     , attribute_id = (select attribute_id `oc_attribute_description` name='attribute_name_here') end if 

i error above:

#1064 - have error in sql syntax; check manual corresponds mysql server version right syntax use near 'if not exists ( select * `oc_product_attribute` product_id = (sele' @ line 1 

note need above in 1 statement. cannot span multiple statements achieve above functionality .. no stored procedures :)

solved:

as pointed out gordon, on duplicate key needed. here final statement works:

insert `oc_product_attribute` values ( (select product_id `oc_product_description` name = 'product_name_here'), (select attribute_id `oc_attribute_description` name='attribute_name_here'), 1, 'xyz' ) on duplicate key update text='abc'; 

but assumes table has either primary key or unique constraint on it, or both.

mysql has special functionality on situation. it's called insert...on duplicate key update. tries insert record if exists, update it. '

in order have functionality working, need have unique constraint on table. based on example, can see checking on 2 columns existence. if have primary key, can add unique constraint on both columns.

alter table oc_product_attribute        add constraint tb_uq unique(product_id, attribute_id) 

once has been executed, can use insert..on duplicate key update

insert oc_product_attribute (product_id, attribute_id, othercol, text) select  max(product_id), max(attribute_id), 1, 'xyz'         (             select  product_id, null attribute_id                 oc_product_description                name = 'product_name_here'             union             select  null product_id, attribute_id                 oc_attribute_description                name='attribute_name_here'         ) x on  duplicate key update text = 'abc'  -- change othercol name of column want insert 1 

• insert ... on duplicate key update syntax