[GoldenLion]

Hi, I've decided to get rid of these "ItemPriceX" columns from my businesses table and create a separate table called "businessprices" that stores every business' item prices. There are 3 columns - "ID", "Item", "Price". When a business is created all the prices are 0 so there's no need of inserting the item price rows. However when you set the item's price I want it to insert a row into "businessprices" if one doesn't exist or update an existing row with the new price. I know there's "INSERT INTO ... ON DUPLICATE KEY UPDATE" for that, but I'm not sure how to use it. I've been googling quite a lot and trying, but I don't know how to get it working. I'd really appreciate that if someone explained me how this works and how to get it working.

[Misiur]

hhttp://sqlfiddle.com/#!9/58f674/1

Here you go. I didn't write constraints and indexes between table relationships (I've never written one by hand), but you should for maximum performance

[GoldenLion]

Quote:

Originally Posted by Misiur hhttp://sqlfiddle.com/#!9/58f674/1 Here you go. I didn't write constraints and indexes between table relationships (I've never written one by hand), but you should for maximum performance

Thanks, I'll have a look.

[ISmokezU]

I learnt it from a member here: https://dev.mysql.com/doc/refman/5.7...duplicate.html

An Example In Pawn:

PHP код:

    mysql_format(MYSQL, query, sizeof(query)
        ,"INSERT INTO jail (Time) VALUES (%d) ON DUPLICATE KEY UPDATE Time = %d"
        , time, time
    );
    mysql_tquery(MYSQL, query, "", ""); 


[Misiur]

ISmokezU: You don't have to repeat your arguments, just use VALUES(column). So, it would be:

pawn Код:

mysql_format(MYSQL, query, sizeof(query)
        ,"INSERT INTO jail (ID, Name, Time, Bail, Cell, Reason) VALUES (%d, '%e', %d, %d, %d, '%s') ON DUPLICATE KEY UPDATE Time = VALUES(Time), Bail = VALUES(Bail), Cell = VALUES(Cell), Reason = VALUES(Reason)"
        , Player[playerid][ID], GetName(playerid), time, bail, GetPlayerVirtualWorld(playerid), reason);
    mysql_tquery(MYSQL, query, "", "");

[GoldenLion]

Quote:

Originally Posted by ISmokezU I learnt it from a member here: https://dev.mysql.com/doc/refman/5.7...duplicate.html An Example In Pawn: PHP код:     mysql_format(MYSQL, query, sizeof(query)         ,"INSERT INTO jail (Time) VALUES (%d) ON DUPLICATE KEY UPDATE Time = %d"         , time, time     );     mysql_tquery(MYSQL, query, "", "");

I checked it as well, but I didn't know which columns need to be unique, but I checked what Misiur wrote and I got it working now.

Quote:

Originally Posted by Misiur ISmokezU: You don't have to repeat your arguments, just use VALUES(column). So, it would be: pawn Код: mysql_format(MYSQL, query, sizeof(query)         ,"INSERT INTO jail (ID, Name, Time, Bail, Cell, Reason) VALUES (%d, '%e', %d, %d, %d, '%s') ON DUPLICATE KEY UPDATE Time = VALUES(Time), Bail = VALUES(Bail), Cell = VALUES(Cell), Reason = VALUES(Reason)"         , Player[playerid][ID], GetName(playerid), time, bail, GetPlayerVirtualWorld(playerid), reason);     mysql_tquery(MYSQL, query, "", "");

Oh, thanks, I didn't know that either.

[ISmokezU]

Quote:

Originally Posted by Misiur ISmokezU: You don't have to repeat your arguments, just use VALUES(column). So, it would be: pawn Код: mysql_format(MYSQL, query, sizeof(query)         ,"INSERT INTO jail (ID, Name, Time, Bail, Cell, Reason) VALUES (%d, '%e', %d, %d, %d, '%s') ON DUPLICATE KEY UPDATE Time = VALUES(Time), Bail = VALUES(Bail), Cell = VALUES(Cell), Reason = VALUES(Reason)"         , Player[playerid][ID], GetName(playerid), time, bail, GetPlayerVirtualWorld(playerid), reason);     mysql_tquery(MYSQL, query, "", "");

Ah, Thanks.