How to use replace to non unique keys? mysql [duplicate]

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
– O. Jones, Commented Sep 28, 2014 at 11:15

gandaliter · Accepted Answer · 2014-09-28 11:17:42Z

1

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.

answered Sep 28, 2014 at 11:17

gandaliter

10.1k1 gold badge19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Ali · Accepted Answer · 2014-09-28 11:22:00Z

0

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

answered Sep 28, 2014 at 11:22

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Collectives™ on Stack Overflow

How to use replace to non unique keys? mysql [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related