CODEWITHSUNDEEP
phpmysql
Novak
Novak

Reputation: 2768

SQL change value, to the next row of another table

Suppose I have the next table structure, table A:

| id |
|----|
| 3  |
| 4  |
| 7  |

Table B:

| id | title | last_id |
|----|-------|---------|
| 1  |   a   |    1    |
| 2  |   b   |    2    |
| 3  |   c   |    3    |

I want to change Table's B last_id to the next matching id from Table A.

Example: (Referring to Table's B first row)

Current last_id is 1, So I need to forward it's value to the next, bigger id from Table A.

So it will go:

1 -> 3 -> 4 -> 7

It can be done also with PHP, but maybe there is a way to keep it in the SQL Server.

Thanks in advance.

NOTE: The server runs MySQL.

Upvotes: 2

Views: 277

Answers (2)

Florent
Florent

Reputation: 12420

The following snippets are pseudo code.

  1. Grab the next table_a id:

    SELECT id FROM table_a WHERE id > $currentLastId ORDER BY id ASC LIMIT 1

  2. If not null, update your table_b last_id column.

Example: (Referring to table_b first row)

The SQL query would be:

SELECT id FROM table_a WHERE id > 1 ORDER BY id ASC LIMIT 1

It would return 3.

Upvotes: 1

Yaroslav
Yaroslav

Reputation: 6544

Previous answer is correct, but the +1 I believe is no needed as you just need value 7

UPDATE table_b
   SET last_id = (SELECT MAX(id) FROM table_a)

Edited:

Now it picks the minimum value from table_a bigger that the last_id on table_b

UPDATE table_b
   SET last_id = (SELECT MIN(id) FROM table_a WHERE id>last_id)

Upvotes: 3

Related Questions