Adding values into a new mySQL db table column depending on an old column

Question

  ID   |  PID  |    NAME   |  VALUE  |
-------------------------------------
  60   |   1   |   Test1   |  9999   |
  21   |   2   |   Test2   |  9999   |
  44   |   1   |   Test3   |  9999   |
  37   |   4   |   Test4   |  9999   |
  24   |   1   |   Test5   |  9999   |

Hey all! I am kind of new to PHP and DBs so I really dont know how to start with this. So I want to want to make a sorting inside a DB where the IDs differ too much. (that means that the first ID starts with 34 and the next one is something like 43 next is 55 etc.)

I have a table which looks like the one above.

Now what I would like to do is changing the values in the column VALUE depending on the values which are in PID.

This means that if in PID the value equals 1 the VALUE on the same row should become 1001 and for the next one 1002, next 1003. If PID = 2 then VALUE should be changed to 2001 then 2002 then 2003 etc. This would be for an already existing table but I would also like to include the VALUE values everytime I add a new statement into that table. So a simple check in pseudocode:

If PID equals 1 then check VALUE column for the highest number that starts with "1" make it +1 and add it into the column of that row

Is that possible to do? what would you guys suggest me to do instead (to make things easier)? If you need further info, tell me please and I will try to explain things better, I dont know if my explanation says what I'm trying to do.

Thank you in advance.

Cheers, K.

Do you use MySQLi or PDO for your database queries? What have you already tried to do? — BeetleJuice
– BeetleJuice, Commented Jul 13, 2016 at 7:20
that is too complected for a sql, you can writ a program to handle the "PID equal" logic or use procedure in mysql — panda
– panda, Commented Jul 13, 2016 at 7:20

sagi · Accepted Answer · 2016-07-13 07:30:29Z

1

You can use UPDATE .. JOIN and join to a derived table containing the "rank" of each ID , and update accordingly :

UPDATE YourTable t
JOIN(SELECT s.ID,s.PID,COUNT(*) as cnt
     FROM YourTable s
     JOIN YourTable s2
      ON(s.pid = s2.pid AND s.id >= s2.id)) p
 ON(t.id = p.id)          
SET t.value = (1000*t.pid) + p.cnt

The inner query here basically "ranks" the data by a self join. It joins to it self by the condition s.pid = s2.pid AND s.id >= s2.id , in words - Same PID that happen before me including me, so the first one will join to 1 record, the second to two and so on.. Then you just update value column to pid*1000 , plus the rank.

edited Jul 13, 2016 at 7:30

answered Jul 13, 2016 at 7:18

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

4 Comments

BeetleJuice Over a year ago

an explanation of how this works would be welcome :-)

sagi Over a year ago

I've added a few words @BeetleJuice

BeetleJuice Over a year ago

SQL is so much more powerful than I realize. So in p table, cnt will be the number of records that the current record was joined to? In other words, if 5 records have PID=1, the record among those with the highest id will have cnt=5, the next cnt=4...?

sagi Over a year ago

Yes, exactly . It's a way to achieve the same as ROW_NUMBER() in other DBMS like Oracle and SQL-Server .

Collectives™ on Stack Overflow

Adding values into a new mySQL db table column depending on an old column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related