Insert subquery when record does not exist...

[dlittle]

Is it possible to provide a default value for table2_id in the
following query if a record does not exist in table2 where name = 'Does
Not Exist'?

Note: We can not add a default value to the database column.

INSERT INTO table1 (my_id, table2_id)
SELECT '01', [table2].my_id
FROM [table2]
WHERE ([table2].name='Does Not Exist')

Thanks -

 

[John Vinson]

Is it possible to provide a default value for table2_id in the
following query if a record does not exist in table2 where name = 'Does
Not Exist'?

Note: We can not add a default value to the database column.

INSERT INTO table1 (my_id, table2_id)
SELECT '01', [table2].my_id
FROM [table2]
WHERE ([table2].name='Does Not Exist')

Thanks -

I'm not sure I understand. If there is no record in existance in
Table2, then that (nonexistant!) record certainly does not contain the
text string "Does Not Exist".

Are you talking about *real* existing records with that text string in
the (misnamed, it's a reserved word) Name field? or are you trying to
handle the case where there exists no record at all in Table2?

John W. Vinson[MVP]

 

[dlittle]

Table2 is populated with records and we are attempting to populate
Table1 from another database.

The following will create a new record in table1.

INSERT INTO table1 (my_id, table2_id)
SELECT '01', [table2].my_id
FROM [table2]
WHERE ([table2].name='Does Exist')

The following does not create a new record in table1. In this
situation we would like to populate the table2_id with a known,
specified value.

INSERT INTO table1 (my_id, table2_id)
SELECT '01', [table2].my_id
FROM [table2]
WHERE ([table2].name='Does Not Exist')

 

[dlittle]

And you are correct it is a misnamed, reserved word "Name" field but
it's not my database.

Thanks for you help -

 

[John Vinson]

Table2 is populated with records and we are attempting to populate
Table1 from another database.

The following will create a new record in table1.

INSERT INTO table1 (my_id, table2_id)
SELECT '01', [table2].my_id
FROM [table2]
WHERE ([table2].name='Does Exist')

The following does not create a new record in table1. In this
situation we would like to populate the table2_id with a known,
specified value.

INSERT INTO table1 (my_id, table2_id)
SELECT '01', [table2].my_id
FROM [table2]
WHERE ([table2].name='Does Not Exist')

I'm still perplexed. Do you mean that you have records in Table2 where
the [Name] field (which should be in brackets, if you're stuck using
it) contains the literal text string "Does Not Exist"?

If so, and if you want to insert 345 as the value of Table2_ID for
each such record, what's wrong with

INSERT INTO table1 (my_id, table2_id)
SELECT '01', 345
FROM [table2]
WHERE ([table2].name='Does Not Exist')

or use '345' if Table2_ID is a Text field.

This will of course create as many duplicating, identical records in
Table1 as there are records with that value of [Name] in Table2;
unless there is an Autonumber field in Table1 you're going to have no
way to distinguish them.

John W. Vinson[MVP]

 

[dlittle]

There are hundreds of records in table2.

The sql insert statements are created via an export from another
system.

Currently when the specified [name] in the insert statement does not
exist in table2 then a new record is not inserted into table1.

Instead of not inserting a record into table1 we need to set the
table2_id with a value, '999999999'.

 

[John Vinson]

There are hundreds of records in table2.

The sql insert statements are created via an export from another
system.

Currently when the specified [name] in the insert statement does not
exist in table2 then a new record is not inserted into table1.

Instead of not inserting a record into table1 we need to set the
table2_id with a value, '999999999'.

Again: let me try to ask this question I've tried to ask, more
clearly.

There are three ways of interpreting your messages in this thread.

One way is that there IS a record in Table2 containing the literal
ASCII text

"Does not exist"

stored in 14 bytes of the field [Name].

Another way to interpret your statements is that the record exists in
Table2 but is NULL, empty.

A third way (the one which makes the least sense but seems to be the
literal interpretation of what you're saying) is that no record exists
in Table2 (but, presumably, there is a record in Table1 which you wish
to update).

Which of these three interpretations is correct?

John W. Vinson[MVP]

 

[dlittle]

I hope this clears it up... I apologize...

There is not a record in Table2 containing the literal ASCII text "Does
not exist".

There are no records in Table1. I am attempting to populate Table1
with insert statements provided from another application.

There are situations where the subquery to select the [table2].my_id
returns no record. The literal ASCII text could be anything. "Does not
Exist", "ASTROS", "CARDINALS", etc.

When the subquery returns no record (NULL ?) then the new record is NOT
created in Table1. Instead of NOT creating a record in Table1 I want
to default/populate the table2_ID with a value so the record is
created.

 

[John Vinson]

I hope this clears it up... I apologize...

There is not a record in Table2 containing the literal ASCII text "Does
not exist".

There are no records in Table1. I am attempting to populate Table1
with insert statements provided from another application.

There are situations where the subquery to select the [table2].my_id
returns no record. The literal ASCII text could be anything. "Does not
Exist", "ASTROS", "CARDINALS", etc.

When the subquery returns no record (NULL ?) then the new record is NOT
created in Table1. Instead of NOT creating a record in Table1 I want
to default/populate the table2_ID with a value so the record is
created.

Ok.... sorry for taking so long to get back.

What you want, then, is when the Subquery in

INSERT INTO table1 (my_id, table2_id)
SELECT '01', [table2].my_id
FROM [table2]
WHERE ([table2].name='Does Not Exist')

returns no records, is to create a new record anyway... right?

I can't think of a good way to do so in a single query. This query
should do it though:

INSERT INTO table1 (my_id, table2_id)
VALUES '01', 99999999
WHERE NOT EXISTS(SELECT Table2.ID FROM Table2 WHERE
[table2].name='Does Not Exist');


John W. Vinson[MVP]