Mysql – Update row contents on some condition

conditionMySQLupdatewindows

For example, let's say I'm building a database for an auction system, in which various users can partake in both selling and bidding on different items, I'd want for an "Auction" instance to also store the highest bidder (Which should obviously be updated each and every time someone bids way higher than the previous highest bid).

How would I achieve something like that?

Best Answer

Split things in two --

Some table(s) would hold the 'current' info, such as the latest bid for a given item.
Some table(s) would keep the 'history', such as a list of all the bid values and who, when, what, etc.

When a new bid comes in, you would do two things: UPDATE a row in the first table and INSERT a row into the second table.

By separating "current" from "history" it may help you think through how to structure the data and formulate the queries.

Related Solutions

Mysql update different fileds set depend on condition

I like the JOIN syntax, it's clearer to me than subqueries:

UPDATE a LEFT JOIN b ON (a.ib=b.i) 
SET i1 = j1, 
    i2 = if(isnull(b.i), j2 ,i2) 
WHERE a.id = jid;

This will change the jid record: i1 to j1 always and i2 to j2 if no match exists, and to the same value if the match exists. You may want to index b.i (probably as a primary key) if there are a lot of records and you want performance.

Mysql – Quite large (400k) thesql database design for multiple users

Use (word_id, user_id) table to store solved combinations, that way you won't store the NULLs. How many words will an average user solve? 5-20 maybe? 400k * 20 = 8M rows, thats nothing (the two column table is quite compact).

Do not even try to alter the table on each new user, that would stop working really quickly.

I suggest InnoDB for anything new unless there is a specific reason against it.

Best Answer

Related Solutions

Mysql update different fileds set depend on condition

Mysql – Quite large (400k) thesql database design for multiple users

Related Question