How to use replace to non unique keys? mysql

Question

I have this table:

rating | businessId | userId
============================
  1          200        1
  2.5        200        2

the user can't has two rows with the same businessId, for example:

    rating | businessId | userId
    ============================
      1          200        1
      2.5        200        1

this's incorrect;

my server receive the businessId,userId and rating.

then it should update the row which has the same userId, and businessId if exist, else create new row.

How could I implement that with one query ?

Is this about MySQL, or about Microsoft SQL Server? You've tagged it with both. — O. Jones, Sep 28 '14 at 11:15

score 1 · Answer 1 · answered Sep 28 '14 at 11:17

It is possible to perform a single query which will either insert or update a row, by using MySQL procedures. However, it's very likely to be better to test whether the row exists first in your chosen language, and then either insert or update as appropriate. This will allow you to give appropriate feedback to the user, and other things which depend on whether the row existed before.

score 0 · Accepted Answer · answered Sep 28 '14 at 11:22

Suppose you get these three values @rating ,@userId,@businessId then you would write you UPSERT (UPDATE/Insert) query something like this.....

IF EXISTS (SELECT 1 FROM dbo.TableName 
            WHERE userId = @userId AND businessId = @businessId)
 BEGIN
        UPDATE dbo.TableName 
         SET businessId = @businessId
            ,rating     = @rating
        WHERE  userId = @userId
 END
ELSE 
 BEGIN
    INSERT INTO dbo.TableName (userId, businessId, rating)
    VALUES (@userId, @businessId, @rating)
 END

How to use replace to non unique keys? mysql

2 Answers2