0

This in not a duplicate of this question. I want to swap values vertically while in other question swap is horizontally.

I have table Products:

id  |   holder_id
--------------- 
1   |   2       
2   |   2   
3   |   13  
4   |   13  
5   |   1       
6   |   12  
7   |   16      
8   |   20  
--------------

Where holder_id is reference to User id. And table conversations:

id  |   user1   |   user2   |   product1    |   product2    
-------------------------------------------------------
..
2   |   12      |   2       |   6           |   2

What I want is to have a single statement to swap holder_id's for two products by conversation id. So for conversation id 2 it should look like this:

id  |   holder_id
--------------- 
1   |   2       
2   |   12  
3   |   13  
4   |   13  
5   |   1       
6   |   2   
7   |   16      
8   |   20  
--------------

So now the holder_id's for id 2 and 6 are swapped.

I have tried:

UPDATE products as p
INNER JOIN conversations as c
ON p.id=c.product1
SET p.holder_id=c.user2
WHERE c.id=2;

and with this I manage two do this:

id  |   holder_id
--------------- 
1   |   2       
2   |   2   //still have to change this
3   |   13  
4   |   13  
5   |   1       
6   |   2   // changed this
7   |   16      
8   |   20  
--------------

Now I have to do onother join to same table and I cant understand how to do that.

Community
  • 1
  • 1
Kārlis Janisels
  • 1,265
  • 3
  • 18
  • 41
  • Do you need to swap? It looks like you have all that is needed to just set the values. It would be 2 queries `UPDATE Products SET holder_id = 2 WHERE id = 2` and `UPDATE Products SET holder_id = 13 WHERE id = 4`. Doing a swap is usually for larger sets of data where the number/values are dynamic – nerdlyist Nov 29 '16 at 20:35
  • Well the problem is that before doing swap I have only both products id's. I actually dont have holder_id's. So I would have to query for them and then do 2 queries you suggested. – Kārlis Janisels Nov 29 '16 at 20:46
  • After tour comment I realized that there could be better way for doing this. I edited question with extra details and where I am stuck – Kārlis Janisels Nov 29 '16 at 21:35

0 Answers0