Selective update in SQL Server

Question

I've created a junction table like this one:

http://imageshack.us/scaled/landing/822/kantotype.png

I was trying to figure out a query that could able to select some rows - based on the PokémonID - and then updating only the first or second row after the major "filtering".

For example:

Let's suppose that I would like to change the value of the TypeID from the second row containing PokémonID = 2. I cannot simply use UPDATE KantoType SET TypeID = x WHERE PokémonID = 2, because it will change both rows!

I've already tried to use subqueries containing IN,EXISTS and LIMIT, but with no success.

You need to provide information about the Pokemon and Type tables and explain why you have two records in the KantoType table per Pokemon. — UnhandledExcepSean, Jan 10 '13 at 13:46
Can you not do a WHERE PokemonID = 2 AND TypeID = 18 for the second row? If not, how do you order your rows - what's the "second" row and what's the "first" row? Ordered by TypeID? — Mark S. Rasmussen, Jan 10 '13 at 13:51
Actually is very simple. If you ever played pokémon, you know that a pokémon can have 1 or 2 types. However, you can note that, in the very first table I provided, I've added 2 types for every pokémon (note: we can consider the TypeID as a default for no second type). About the rows' order, there isn't actually one: It all depends on the pokémon you are considering. For example, you may 2 pokémons which have the same type (steel and rock, for example), but the first pokemon may have its primary type as rock and the other as steel... — Mudkip, Jan 10 '13 at 14:37
Actually, I intend to use this query in a C# project. Here are some useful links: http://imageshack.us/photo/my-images/708/pokmonform.png/ http://imageshack.us/scaled/landing/401/diagramf.png — Mudkip, Jan 10 '13 at 14:38
I'm afraid I haven't played Pokemon. There are two options. Either you know the type you're updating, in which case you can use filter on both PokemonID AND TypeID in your query. Or if you don't know the TypeID but simple want to update the "second" row - then you need to define how rows are ordered (for example, by TypeID ascending/descending, or some other column), so you can tell SQL Server which row is the second. The SQL for the second option has been provided by Mahmoud Gamal. — Mark S. Rasmussen, Jan 10 '13 at 14:53

Mahmoud Gamal · Answer 1 · 2013-01-10T13:55:57.193

Its unclear what are your trying to do. However, you can UPDATE with JOIN like so:

UPDATE 
SET k1.TypeID = 'somethng' -- or some value from k2
FROM KantoType k1
INNER JOIN
(
   Some filtering and selecting
) k2 ON k1.PokémonID = k2.PokémonID 
WHERE k1.PokémonID = 2;

Or: if you want to UPDATE only the two rows that have PokémonID = 2 you can do this:

WITH CTE
AS 
(
   SELECT *,
     ROW_NUMBER() OVER(ORDER BY TypeID) rownum
   FROM KantoType
   WHERE PokemonID = 2
) 
UPDATE c 
SET  c.TypeID = 5
FROM CTE c
WHERE c.rownum = 1;

SQL Fiddle Demo

score 0 · Answer 2 · answered Jan 10 '13 at 13:57

I can suggest something like this if you just need to update a single line in your table:

UPDATE kantotype
SET 
  type = 2
WHERE pokemon = 2
AND NOT EXISTS (SELECT * FROM kantotype k2 
                WHERE kantotype.type > k2.type
                   AND kantotype.pokemon = k2.pokemon)

It would be easier to get the first or last item of the table if you had unique identifier field in your table.

score 0 · Answer 3 · answered Jan 10 '13 at 14:04

Not sure even if you are trying to update the row with PokemenID =2 by doing a major filtering on TypeID... So just out of assumptiong (big one), you can give a try on Case

UPDATE yourtable a
LEFT JOIN youtable b on a.pokeid = b.pokeid
SET a.typeid = (CASE
   WHEN a.typeid < b.typeid THEN yourupdatevalue
   WHEN a.typeid > b.typeid THEN someothervalue   
   ELSE a.typeid END);

score 0 · Answer 4 · answered Jul 11 '13 at 13:36

If you know the pokemon ID and the type id then just add both to the where clause of your query.

UPDATE KantoType 
SET TypeID = x 
WHERE PokémonID = 2
AND TypeID=1

If you don't know the type ID, then you need to provide more information about what you're trying to accomplish. It's not clear why you don't have this information.

Perhaps think about what is the unique identifier in your data set.

Selective update in SQL Server

4 Answers4

SQL Fiddle Demo