Jump to content
  • Advertisement

Archived

This topic is now archived and is closed to further replies.

Ironica

SQL : Need Some Help With A Query

This topic is 5656 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey. I was wondering whether it''s possible to do this in a single query, or whether I''d have to run 2. I have a game database, with a table named tbl_flag. It contains some flags for the characters in the game. It has 3 columns, name (char), flag (unsigned int) and value (unsigned int). Now I want to insert a new row. The name can be duplicate, the flag can be duplicate, but the name AND the flag cannot be duplicate. If they are, I want to overwrite the row. I checked the manual, and I figured I have to use an INSERT statement, with ON DUPLICATE KEY or something. But I''m not sure which columns to set as primary keys. And I''m not sure if it can be done, because I need to check for 2 duplicates. Anyone know if this can be done, and maybe help me out with the query? Or will I have to run a SELECT statement, check if there''s a row with the same two values, and if there is, run an UPDATE statement, or if not, run an INSERT statement?

Share this post


Link to post
Share on other sites
Advertisement
If i understand correctly, then the two columns you want as primary keys are:
NAME
and
FLAG since you said name and flag cannot be the same. so make this a primary key...and how I would do this is yes use a SELECT on the primary key, if it exists then do an update, if not then do an insert, i dont think off hand I could think of a way to do this in one statement, then again its been a while since ive done some hardcore SQL so maybe there is

good luck,
Shane

Share this post


Link to post
Share on other sites
Hi,

Are you using SQL Server? Its just that I''ve not seen the ON DUPLICATE statement before!

I would make the Flag and Name columns the primary key for the table. Then, try to insert the data into the table. If a primary key contraint is violated, then this means you are inserting a Flag and Name that already exists. Capture this error and then perform an update.

Or, you can perform a SELECT to see if a Flag and Name row already exists and perform an INSERT or UPDATE base on the result.

So, you can either check before doing the work or you can respond to an error and correct it.

Hope this helps.

Mike

Share this post


Link to post
Share on other sites
Thanks for the replies. Yes, I am using MySql Server.

This is what I saw in the manual:
quote:

If you specify ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a PRIMARY or UNIQUE key, an UPDATE of the old row is performed. For example, the command:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
--> ON DUPLICATE KEY UPDATE c=c+1;

in case of column a is declared as UNIQUE and already holds 1 once, would be identical to the
mysql> UPDATE table SET c=c+1 WHERE a=1;

Note: that if column b is unique too, the UPDATE command would be written as
mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

and if a=1 OR b=2 matches several rows, only one row will be updated! In general, one should try to avoid using ON DUPLICATE KEY clause on tables with multiple UNIQUE keys. Since MySQL 4.1.1 one can use function VALUES(col_name) to refer to the column value in the INSERT part of the INSERT ... UPDATE command - that is the value that would be inserted if there would be no duplicate key conflict. This function especially useful in multiple-row inserts. Naturally VALUES() function is only meaningful in INSERT ... UPDATE command and returns NULL otherwise. Example:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
--> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

The command above is identical to
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
--> ON DUPLICATE KEY UPDATE c=3;
mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
--> ON DUPLICATE KEY UPDATE c=9;

When one uses ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.
If MySQL was configured using the DONT_USE_DEFAULT_FIELDS option, INSERT statements generate an error unless you explicitly specify values for all columns that require a non-NULL value. See section 2.3.3 Typical configure Options.
You can find the value used for an AUTO_INCREMENT column with the mysql_insert_id function. See section 8.1.3.130 mysql_insert_id().



I''m not that good at SQL, I just know the basic SELECT, INSERT etc querys. The thing is, I don''t want to UPDATE if name OR flag is duplicated. I want to UPDATE if name AND flag is duplicated.

I''ll try a little more to explain how the table works... Each character has an amount of flags each, and each flag has a value...

name flag value
Character1 0 0
Character1 1 20
Character1 2 1
Character2 0 1
Character2 1 15
Character2 2 0

Do you see? I can have multiple rows for Character1, because Character1 has multiple flags. And I can have mulitple rows for flag 0, because multiple characters have flag 0. But Character1 does not have 2 flag 0''s, and no character may have 2 flags of the same number.

And my statement basically needs to change the value of flag x for character y. But if the row flag x, character y doesn''t exist, it doesn''t matter, just create it and set the value.

Share this post


Link to post
Share on other sites
Shooting from the hip:

recordset("SELECT * FROM [YOURTABLE] WHERE FLAG=x AND NAME = y")

if recordset>1 then
UPDATE the proper record
else
INSERT a new record



I see one problem, which one do you want to update, if there is a duplicate, or is it assumed there should never be a duplicate of name and flag?


Share this post


Link to post
Share on other sites
Yes thanks, that''s what I am doing. I was just hoping that maybe there would be a way to do it all in a single query.

The game will never create a duplicate of name and flag, so it''s always assumed that there isn''t one. This function I am writing is the only function which writes to this table. That''s what I''m doing, is coding the function so that it won''t create a duplicate. If there is a duplicate, I guess all of them will be updated. Which won''t really affect reading from the database, so shouldn''t cause any problems.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
you seem to be missing the point that you can create tables with primary keys and/or unique indexes that have multiple columns, i.e., they are concatenated. if you create the table with such a pk or unique index for your name/flag combination then it would be safe to use the on duplicate key clause of the insert statement to handle updating of the value column.

Share this post


Link to post
Share on other sites
You say you are using MySQL? You can do what I do (not sure about speed but it shouldnt be any slower)

REPLACE tablename SET Value=100 WHERE Name="CharacterName" AND Flag=0;  


I think this only works on versions 4 and above but it will do exactly what you want it too. Search the mysql docs for more information.

[edited by - ISOPimp on June 18, 2003 1:56:49 PM]

Share this post


Link to post
Share on other sites
quote:
Original post by Anonymous Poster
you seem to be missing the point that you can create tables with primary keys and/or unique indexes that have multiple columns, i.e., they are concatenated. if you create the table with such a pk or unique index for your name/flag combination then it would be safe to use the on duplicate key clause of the insert statement to handle updating of the value column.

Keep in mind I know next to nothing about MySQL, or any databases for that matter. Could you explain how to do this please?


quote:
Original post by ISOPimp
You say you are using MySQL? You can do what I do (not sure about speed but it shouldnt be any slower)

REPLACE tablename SET Value=100 WHERE Name="CharacterName" AND Flag=0;   


I think this only works on versions 4 and above but it will do exactly what you want it too. Search the mysql docs for more information.


REPLACE? Umm... Is this pretty much the same as UPDATE? Because I want to INSERT it as a new row, if it doesn''t exist already. I do have it working already, but I''m reading out using SELECT, then conditionally using INSERT / UPDATE, depending on whether it already exists or not. I just thought it would be more efficient if I could do the whole thing using MySQL.

Share this post


Link to post
Share on other sites
I would do it this way
first delete the row if there is one and then always insert the new one

delete from table WHERE Name="CharacterName" AND Flag=0;
insert into table values (....)
no check required makes the query easier, or is there a problem with it which i do not see?

so you can put it in a function on the sql server
and in your program it is only one select statement

k.



[edited by - konvenienz on June 19, 2003 6:21:00 AM]

Share this post


Link to post
Share on other sites

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!