Maybe I am missing something very obvious.
Write me a generic SQL statement(s) that sets path_id = 331000 for a creature with guid = 33100 without first checking if there is a creature_addon entry for that guid that may have mount, bytes1, bytes2 and emote fields set with values that we want to preserve.
Here’s how you would do it if the tables were normalized properly:
UPDATE creature SET path_id = 331000 WHERE guid = 33100;
Update c_a set path_id =y where guid=x
If you get a Mysql error catch it and issue a basic insert
Alternatively this may work (not sure that’s legit syntax but I’m assuming it should) :
Replace into c_a select x, , , from c_a where guid=x
That may even work in both cases, not sure
I don’t quite see your point, exceptions are easy enough to catch…
Using replace would be a better way to go. Catching an exception on the delete is pretty ugly.
The point I was trying to make is that the sql statements you have to make with the current tables are a lot more complicated than if the tables were normalized properly.
A delete doesn’t trigger exception, but I meant catching Mysql errors, be it in c or with php’s mysql_* or even PDO.
You can use INSERT … ON DUPLICATE KEY UPDATE, and problem solved.
SET @GUID = 33100;
SET @MOUNT =(select mount from creature_addon where guid=@GUID);
SET @BYTES1 =(select bytes1 from creature_addon where guid=@GUID);
SET @BYTES2 =(select bytes2 from creature_addon where guid=@GUID);
SET @EMOTE =(select emote from creature_addon where guid=@GUID);
SET @AURAS =(select auras from creature_addon where guid=@GUID);
REPLACE INTO creature_addon
SET path_id = @GUID*100,
guid = @GUID,
mount = @MOUNT,
bytes1 = @BYTES1,
bytes2 = @BYTES2,
emote = @EMOTE,
auras = @AURAS;
ITS cool or not ?? /emoticons/default_smile.png Damn we have to set ISNULL or COALESCE when is null value cause it will make an error and I don’t know how to do this.
ahhaha this is the new E=Mc2
/emoticons/default_smile.png
i was joking
ok. I will normalize the tables properly on my repo. TC on origin can continue to work the way it wants to work.
baric, I am with you. I makes absolutely no sense to have the _addon tables. Way back when, it made sense due to the fact that not all creatures needed those fields, and it was a waste of DB space. That is still true, but the small gain you get just isn’t worth it anymore.
In large relational databases, it makes sense to break things out into as many tables as possible if there would be a lot of NULL values, TDB isn’t a large database…
– Brian
Long ago, I said a lot of tables need to be combined for ease of use, and was told that they were done that way because that is how they are sent in the packets to and from the sever, as if the code to send packets can’t pull out just the info it needs to send from a larger set of fields… if this thinking always won out, we might still have the player blob… shudders