Update multiple rows with different values.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update multiple rows with different values.

Author

Topic

Stefan
Starting Member

11 Posts

Posted - 2006-08-02 : 10:53:17

Hey
I use php and im trying to update some database data.
Now im not that good in SQL and i was wondering how you have to handle the following issue:
My project is a project that lets you save game clanwar scores and info.
Now each game can exist of multiple rounds. And each round a different map is played.
On the page where the user needs to enter the results i have a select box for each round and these select boxes are filled with the map names.
If the user change these select boxes the record of that round in the database need to be update with the new map thats selected.
I could do 1 query for every round like this:
UPDATE played_wars SET map=$map_id WHERE war_id=$war_id AND round_id=$round_id
However if 2 rounds need to be changed then i would need to do 2 queries:
UPDATE played_wars SET map=$map_id WHERE war_id=$war_id AND round_id=$round_id
UPDATE played_wars SET map=$map_id2 WHERE war_id=$war_id AND round_id=$round_id2
and if 5 rounds were played i'd need to do 5 queries. Which isnt very efficient.
How can i integrate for example the 2 queries above in 1 query?

i hope you can help me.
Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-02 : 12:09:55

Try

Begin Transaction

UPDATE played_wars SET map=case when war_id=$war_id AND round_id=$round_id then $map_id when war_id=$war_id AND round_id=$round_id2 then $map_id2 end

Select * from played_wars

If you are satisfied with the results then,

Commit Transaction

Otherwise

Rollback Transaction

Madhivanan

Failing to plan is Planning to fail

Stefan
Starting Member

11 Posts

Posted - 2006-08-02 : 13:12:54

Works perfectly. thank you :)

Stefan
Starting Member

11 Posts

Posted - 2006-08-02 : 13:27:35

// nevermind i found my answer.

Stefan
Starting Member

11 Posts

Posted - 2006-08-02 : 14:55:59

Ok i finnally made the following query:


UPDATE played_locations AS pl,
       wars AS wars
SET    pl.location_id = CASE 
           WHEN   pl.for_war=26 AND pl.round=1 THEN 3
           WHEN   pl.for_war=26 AND pl.round=2 THEN 3 END,
       pl.opponent_score = CASE 
           WHEN pl.for_war=26 AND pl.round=1 THEN 73
           WHEN pl.for_war=26 AND pl.round=2 THEN 78 END,
       pl.own_score = CASE
           WHEN pl.for_war=26 AND pl.round=1 THEN 80
           WHEN pl.for_war=26 AND pl.round=2 THEN 80 END,
       wars.finished = 1 WHERE wars.id=26, 
       wars.score_own = 1 WHERE wars.id=26,
       wars.score_opponent = 0 WHERE wars.id=26;

Thats how the code looks when i display the sql query on the page.
Normally the numbers are replaced by variable names.

The error i get is:

quote:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' wars.score_own = 1 WHERE wars.id=26, wars.score_oppone

PS. All fields in this query are either of the type int or Tiny int.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-02 : 15:26:02

You can't update two tables at once.

UPDATE played_locations
SET    location_id = CASE 
           WHEN round in (1, 2) THEN 3
	   ELSE location_id END,
       opponent_score = CASE 
           WHEN round = 1 THEN 73
           WHEN round = 2 THEN 78
           ELSE opponent_score END,
       own_score = CASE
           WHEN round in (1, 2) THEN 80
           ELSE own_score END
WHERE  pl.for_war = 26
 
UPDATE wars
SET    finished = 1, 
       score_own = 1,
       score_opponent = 0
WHERE  wars.id = 26

Peter Larsson
Helsingborg, Sweden

Stefan
Starting Member

11 Posts

Posted - 2006-08-02 : 16:31:53

Thanks :)

You can update 2 tables at once though. I saw in your code that i had the WHERE statements wrong.

Here's the code i have now which is working perfectly, but its mixed with some PHP.
The variables are strings containing where clauses.


// UPDATE SCREENSHOT, LOCATION AND SCORES FOR EACH ROUND.
$query = "UPDATE played_locations AS pl, wars AS wars SET
          pl.location_id= CASE " . $query_location_constr . " END,
		  pl.opponent_score = CASE " . $query_opponentscore_constr . " END,
		  pl.own_score = CASE " . $query_ownscore_constr . " END,
		  pl.screenshot = CASE " . $query_screenshot_constr . " END,
		  wars.finished = 1,
		  wars.score_own = ".$total_own_score.",
		  wars.score_opponent = ".$total_opponent_score."
		  WHERE wars.id=".$war_id;
$query_result = @mysql_query($query,$conLink);
checkQueryResult($query_result,"Failed to save round data",1);
exit();

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-03 : 09:57:22

your MySQL server version

This is a MS SQL SERVER board, which uses T-SQL.

Nice that you found an answer anyways.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-03 : 11:14:02

Try at www.MySQL.com or www.DBForums.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources