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

score 1 · Answer 1 · answered Mar 14 '17 at 17:50

1

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.

answered Mar 14 '17 at 17:50

Read Hughes

53
9

TOP only works for SQL Server, OP is using PL SQL which is usually Oracle. – CathalMF Mar 15 '17 at 18:14

score 0 · Accepted Answer · answered Mar 14 '17 at 17:40

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.

SQL - Limit the values in an Insert from another table

2 Answers2