SQL - Limit the values in an Insert from another table

Question

im trying to insert the following:

insert into TABLEA select b.ID,..... from TABLEB b where code = 'NL'

the problem is that select * from TABLEB where code = 'NL'; returns more than 1 value.

is there anyway to limit that to just 1 value?

i tried select min(b.id) but it didnt work

Answer 1

use where rownum = 1

insert into TABLEA select b.ID,..... from TABLEB b where code = 'NL' and rownum = 1

But are you sure you want to do this? What if its actually the 2nd row you want. You might want to figure out why your query is returning more than one.

Answer 2

from what I can gather you want just one instance of where code = NL to be inserted even though there are multiple records that fit this.

If this is what you want, change your "select *" to "select top 1 *" and it will only pull one record from your sub query.