CODEWITHSUNDEEP
mysql
qrikko
qrikko

Reputation: 2603

Moving data from one row to another

I'm not quite sure if I'm thinking about this the wrong way, so if that's the case perhaps someone will point me in the right direction. If however I am just missing the concept then perhaps I can get help with that.

So any way I'll win.. or something.

What I want to do is something like this, given a table in a database, in a single query move a specific value to another row.

To clarify take this table as an example:

id I value
0 I 5
1 I 2

I want to move value for id=0 so that the table become:

id I value
0 I 0
1 I 5

(OBSERVE! the value at id=1 doesn't matter, it's overridden and the id=0 _value_ is just set to zero)

My attempt this far have been a query something along the lines of:

UPDATE atable 
SET item = CASE id 
WHEN '1' THEN (SELECT value FROM atable WHERE id='1') 
WHEN '0' THEN '0' 
END;

However this is not valid and I can't use the SELECT in that fashion. So my take #2 were:

UPDATE atable 
SET item=(SELECT value FROM atable WHERE id='1') 
WHERE id='0';

Which kind of works, however I can't find a way to then set value=0 for id=0

I hope it all makes sense and aren't all just ramblings now.

Just to try to be all clear: I want to move the value from one row to another, so copy will not do I can figure that one out. AND I want to do it in one query, of course I could split it up into two queries and it would be simple

Thank's for your time reading this.

Upvotes: 2

Views: 1323

Answers (4)

qrikko
qrikko

Reputation: 2603

I believe that I am going with the "easy" solution since I actually do have more information about the problem I don't per say need to read it dynamically.

So for my particular problem I decided to solve it like this:

I have these parameters (in php):

$fromSlot, $toSlot, $itemID

And I want to move $itemID from one slot to another.

But since I know the id of the item I may as well just insert that into the $toSlot and set the item of $fromSlot to zero.

Basically the table has a slotID, itemID where slotID is the unique key and itemID is an id to reference an item in that slot.

My query became:

UPDATE `slots` SET item = CASE slotID
WHEN '$fromSlot' THEN '0'
WHEN '$toSlot' THEN '$itemID'
END;

This way I don't need to be concerned by the fact that it might first set the $fromSlot to zero and then "copy" that zero into $toSlot.

I wanted to avoid sending the $itemID all together but realized it really isn't a huge problem and as I rather avoid making two queries then sending the extra parameter.

The alternative I suppose would be to skip sending $itemID and then first update the $toSlot with UPDATE slots SET item = (SELECT item FROM slots WHERE slotID='$fromSlot') and then a second query to set the $fromSlot.item

This is how I'll solve it.

Any cleaver ideas or whatnot are of course welcome, but I'll mark this as my accepted answer.

If nothing else maybe it help people follow my train of thoughts and understand why I asked (and answered -_-) this question.

Upvotes: 0

Esen
Esen

Reputation: 973

you could do this with one query but it is only possible if the ID is expected to be sequencial

update a set a.value=isnull(b.value,0) from atable a left outer join atable b on a.id=b.id+1

EDIT

update a set a.value=ifnull(b.value,0) from atable a left outer join atable b on a.id=b.id+1

Upvotes: 1

JHS
JHS

Reputation: 7871

I think this will help - 

UPDATE atable a,
(SELECT value, id FROM atable) c
SET a.value = c.value 
WHERE a.id < @MAX(id)
AND a.id = c.id + 1;

The only condition is that the id's should be in sequence and you should not have the safe update mode active.

Upvotes: 2

Mark Wilkins
Mark Wilkins

Reputation: 41222

This type of thing is usually performed in a transaction to make it behave as an atomic operation. For example:

start transaction;    
set @myval = (select value from atable where id = 0);
update atable set value = @myval where id = 1;
update atable set value = 0 where id = 0;
commit;

It would be possible to "move" the value from the first row to the other row with a single statement, however it is extremely ugly looking and probably less efficient. I think the following might do that, but I don't think it would be a good idea.

update atable set value = 
      (select x.value from 
            (select value from atable where id = 0) x) 
   where id = 1;

Upvotes: 1

Related Questions