CODEWITHSUNDEEP
phpmysqldatabaseinsert
Dainy
Dainy

Reputation: 89

mysql update if exists otherwise update when I don't have the unique key

A similar question has been asked before, and the answer was to use

INSERT INTO table ... ON DUPLICATE KEY UPDATE ...

However, this requires the duplicate key to have unique property. What if I don't and cannot have unique property?

The detail is as follows: I have a tablewith 4 columns:

id  col2 col3 count

If both col2 and col3 values exist, then increase count, otherwise insert this item. How could I do that with one MySQL query?

UPDATE

I realized only after posting this question that I can set unqiue key for more than one column. For example, if I do not want to have col2 and col3 duplicate, I can execute the following sql to make the combined column a unique one:

ALTER TABLE `table` ADD UNIQUE unique_index (`col2`, `col3`);

Upvotes: 0

Views: 1037

Answers (3)

AddWeb Solution Pvt Ltd
AddWeb Solution Pvt Ltd

Reputation: 21691

Dainy,

I think you are looking to insert record if it doesn't exist then to update specific column value, right..?

And for that, you require to create a procedure like this:

delimiter $$
create procedure update_or_insert()
begin
  IF EXISTS (SELECT * FROM tablename WHERE col2 = 'something2' AND col3 = 'something3') THEN
    UPDATE tablename SET count = count + 1;
  ELSE 
    INSERT INTO tablename (col2, col3, count) VALUES ('something2', 'something3', 'something4');
  END IF;
end $$
delimiter ;

and call that procedure like:

call update_or_insert();

Hope this helps you out.

Thanks!

Upvotes: 0

Noman
Noman

Reputation: 4116

Here you can use insert on duplicate MYSQL Reference.

INSERT INTO table (col1,col2,`count`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `count`=(col2+col3)

Upvotes: 1

Mohammad Zare Moghadam
Mohammad Zare Moghadam

Reputation: 688

are you want this?

    IF EXISTS(--Query to check for the existence of your condition here)
    BEGIN
      --UPDATE HERE
    END ELSE
    BEGIN
      --INSERT HERE
    END

Upvotes: 0

Related Questions