Update table data using loop postgres

Question

I have 2 tables, stock and store

stock:

|-----------------|                      
|  Id | store_id  |
|-----|-----------|                        
|   1 |           |                                 
|-----|-----------|
|   2 |           |  
|-----|-----------|                                           
|   3 |           |  
|-----|-----------|                              
|   4 |           |                                                             
|-----|-----------|

store:

|-----------------|
|  Id | name      |
|-----|-----------|
|   21|   aaaa    |  
|-----|-----------|
|   12|    bbbb   |  
|-----|-----------|

I need to loop over store table and and put its id in stock table's store_id column, the result should be:

|-----------------|                      
|  Id | store_id  |
|-----|-----------|                        
|   1 |    21     |                                 
|-----|-----------|
|   2 |    12     |  
|-----|-----------|                                           
|   3 |    21     |  
|-----|-----------|                              
|   4 |    12     |                                                             
|-----|-----------|

I have tried some example like Postgresql Update inside For Loop

But didn't get a solution... plz help

You don't want to use a loop. A loop is almost always the wrong solution in SQL. How are you determining which store_id goes with each id in stock? — Jeremy
– Jeremy, Commented Nov 13, 2019 at 18:23
@AbhishekKumar When saying "first", do you mean ordered by stock id? Or something else? By "and so on", you mean alternatingly? — Bergi
– Bergi, Commented Nov 13, 2019 at 20:25

Jeremy · Accepted Answer · 2019-11-13 18:48:01Z

0

I'm a bit skeptical that this is what you actually want, but from what you describe, it seems like this is the answer:

UPDATE STOCK SET store_id = (CASE WHEN id%2=1 THEN 21 ELSE 12 END);

answered Nov 13, 2019 at 18:48

Jeremy

6,77322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update table data using loop postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related