SQL Query help

[lerxst2112]

SELECT AVG(`hp`) FROM `npc_types` WHERE `level` = 45;

You may want to add to the WHERE so you don't select guards, pets, etc. You'd just need to figure out how to select them so you can exclude them.

[reddogut]

How about this?

Select AVG(hp) from npc_types where level = 45 AND merchant_id = 0 AND 'name' NOT LIKE '%Guard%' AND 'name' NOT LIKE '%pet%' AND 'name' NOT LIKE '%Sum%';

I get 4224 average hitpoints out of this query.

[reddogut]

Sorry, dup post

[reddogut]

Ok, so I want to change the hitpoints of all level 50 mobs... But I don't want to change the hitpoints for Guards, pets or merchants. So I came up with this...

UPDATE npc_types SET hp = 3000 WHERE LEVEL = 50 AND merchant_id = 0 AND name NOT LIKE 'Guard_%';

Now AFAICT the id's for the pets in the npc_types are all between 501 and 770. What should I add to the above query to exclude those id's?

Thanks,
Mike

[Derision]

UPDATE npc_types SET hp = 3000 WHERE LEVEL = 50 AND merchant_id = 0 AND name NOT LIKE 'Guard_%' AND (id < 501 OR id > 770);