SQL Insert - Only insert on this condition

Question

I have Table 1:

ID UserString Date ------------------------- 1 KRBS 4/25/2014 2 WEFG 4/24/2014

And Table 2:

ID UserString + Other user info ---------------------------------- 1 KRBS + . . .. .

I'm preforming insert into Table 1 but I would like to make a condition to only insert if the user is already available in Table 2 (only only insert row in table 1 if user already exist in table2)

I'm currently doing two separate SQL checks (one if user exist then insert) but I'm sure there is a better way

Is this MySQL or SQL Server? It isn't obvious from your tags... — Tomas Pastircak
– Tomas Pastircak, Commented Apr 24, 2014 at 17:39
you can try an insert into t1 (userstring, date) select 'newuserstring', 'newdate' from othertable where ... — Marc B
– Marc B, Commented Apr 24, 2014 at 17:41
@MarcB wouldn't it raises an error if there was not nullable fields in Table1 and no results found in Table2? — user3303864
– user3303864, Commented Apr 24, 2014 at 17:48
@andre: it'd be a 1:1 thing. 5 rows of data found in table2, 5 rows inserted into table1. 0 rows found in table2, 0 rows inserted into table1. — Marc B
– Marc B, Commented Apr 24, 2014 at 17:50

Tomas Pastircak · Accepted Answer · 2014-04-24 17:50:58Z

Best way to achieve this is to define a FOREIGN KEY on the table. This is the simplest and most implicit way to do it, some reference on that can be found on MSDN. That means you will not ever be able to a) insert an entry to this table if no corresponding FK exists, and b) delete from the base table if entries with foreign key are here (in your case, delete the user if it already has the settings ). It will look like:

ALTER TABLE NameOfTheTable ADD CONSTRAINT FK_SomeNameForTheKey FOREIGN KEY (nameOfColFromThisTable) REFERENCES NameOfTheOtherTable (NameOfTheCOlumnYouAreReferencing);

maybe he is trying to prevent an error being unnecessarily show in his screen, or error log. I think that checking is still useful. But the Foreign Key is always a good point. +1 ;).
Appreciate everyone's help. I find this to be the best solution Cheers :)

user3303864 · Accepted Answer · 2014-04-24 17:43:39Z

try this on mysql:

if {{ userId }} in (select UserString from Table2 where UserString = {{ userId }}) then insert into Table1 (ID, UserString, Date) values ({{ id }}, {{ userId }}, '{{ date }}') end if

or this on sql server

if {{ userId }} in (select UserString from Table2 where UserString = {{ userId }}) insert into Table1 (ID, UserString, Date) values ({{ id }}, {{ userId }}, '{{ date }}')

change {{ var }} to your actual values

timo.rieber · Accepted Answer · 2014-04-24 17:54:15Z

You may want to use insert ... select ... to achieve this, assuming your ID column is auto increment:

insert into table1 ( UserString, Date ) select table2.UserString, @yourDateVariableHere from table2 where table2.UserString = @yourUserStringVariableHere

Collectives™ on Stack Overflow

SQL Insert - Only insert on this condition

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related