CODEWITHSUNDEEP
sqldatabasepostgresql
Adam
Adam

Reputation: 532

Insert one line into database where multiple rows are possible

I'm using PostgreSQL and have a table as follows:

Table name: ip4_bank

Field 1: ip4_address

Field 2: nic_id

By default this table will contain a list of IPv4 addresses with field 2 set to null. What I want to do is run a query to assign a nic_id to the next available IP4 address.

The query I have so far is:

INSERT INTO ip4_bank (nic_id) VALUES ('1') WHERE nic_id = null

but this will fill up the table with nic_id of '1' where all were previously set to null.

Anyone have any ideas on how to just insert the nic_id value into just one row?

Upvotes: 0

Views: 340

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

Assuming that 'next available' is the lowest IP, try this: 

UPDATE ip4_bank SET nic_id = 1
WHERE nic_id is null
AND ip4_address = (SELECT MIN(ip4_address)
                   FROM ip4_bank
                   WHERE nic_id is null)

Upvotes: 3

Related Questions