SQL insert if certain name not in a column-CodePudding

I want to insert records into a table:

UserType|UserName|VALID
Type1     name1    True

...

Currently I am using the following sql code:

Insert into TABLE_NAME select 'certain type', 'a user's name', 1

but if 'a user's name' has been recorded in the table, that will cause duplicated names in UserName column.

In other words, I want to insert a row of record if 'a user's name' does not show up in the UerName column. How can I achieve that?

CodePudding user response：

You can add an not exists condition. This should work in most rdbms:

insert into t(usertype, username, valid)
select 'type1', 'name1', 1
where not exists (
    select 1
    from t
    where username = 'name1'
)

And if values tvc is supported then:

insert into t(usertype, username, valid)
select *
from (values
    ('type1', 'name1', 1)
) as va(usertype, username, valid)
where not exists (
    select 1
    from t
    where t.username = va.username
)

CodePudding user response：

If i understood you correctly you want to enforce unique values within column in a table.

In that case you can either use unique constraint on a column, or if that's MSSQL (T-SQL) you can do something like this:

IF NOT EXISTS(SELECT TOP 1 1 FROM table_name WHERE column_name='a user''s name') BEGIN
   INSERT INTO table_name (UserType, UserName, VALID) VALUES ('certain type', 'a user''s name', 1)
END