Multiple database updates as atomic operation?

[patbob]

In my game, I need to move a pawn from one square on the board to another. So far, the only way I've gotten this to work, is via two separate database updates:

• UPDATE board SET board_player=NULL WHERE ( board_row = 6 AND board_col = 1 )
• UPDATE board SET board_player='2321571' WHERE ( board_row = 6 AND board_col = 3 )

using two calls to DbQuery().

Is there any way to combine these two queries into a single one? I'm more concerned with atomicity, but it would probably have better performance too.

[RicardoRix]

db queries are really fast, I wouldn't worry about it.
you can enter 2 queries by a seperated by a ;

you could have a different setup where there is db table of balls with positions rather than a table of squares/cells. Then it would be one update query.

[jmcl99]

I wouldn't worry about atomicity either, as your code is part of a single database transaction.

See http://en.doc.boardgamearena.com/Main_g ... e.game.php ("Accessing the database" section).

[Brainchild]

As previous replies pointed out, there isn't really a reason to do this. But if you insist, you can use MySQL's IF or CASE.

https://stackoverflow.com/questions/217 ... ct-queries

[patbob]

Thanks you all. I'd tried using two statements separated with a ';', but it wouldn't work. I'll try it again since my SQL-foo is very weak and I might have missed something. I haven't tried the IF-CASE thing, so I'll give that a try too. I'll post back here if I get something working.

I think RicardoRix had the right idea though -- don't keep the entire board as a database, but rather the positions of the playing pieces on the board. But that better design choice will have to wait for my next game