Posted by Ed Murphy on 02/22/07 17:06

dhek wrote:

>>> I have a very simple issue: for simplicity lets say I have 2 tables, A
>>> and
>>> B.
>>> - Table A contains 5 fields. Amongst these there is a 'id'-field
>>> which
>>> is but a reference to table B.
>>> - Table B contains 2 fields: 'id' and 'text'
>>>
>>> In order to post data to table A I thus (from a known text value that
>>> should
>>> match 1 value in B.text) have to get the value of B.text before
>>> performing
>>> the UPDATE/INSERT statement.
>>>
>>> How is this possible?
>>>
>>> I would have thought something like
>>>
>>> INSERT INTO A (val1, val2, val3, ID, val4)
>>> VALUES ('x1','x2','x3', SELECT id FROM B WHERE [SOME TEXT VALUE] =
>>> B.text,
>>> 'x4')
>>>
>>> however this is not possible, so I'm lost - not experienced in the arts
>>> of
>>> SQL:-)
>>>
>>> Hope someone can help.
>>>
>>> Best Regards,
>>> Daniel
>> Try something more like this:
>>
>> INSERT INTO ATable(val1, val2, val3, ID, val4)
>> SELECT 'x1', 'x2', 'x3', b.id, 'x4'
>> FROM BTable b
>> WHERE b.Text = ['Your Text Here']
>
> But this is not possible since table B only contains 2 fields (id, and text)
> or am I misunderstandig u?

The SELECT portion only gets one of its five values (b.id) from
table B; it gets the other four from the values provided directly
on the SELECT line (which, in practice, might instead be input
parameters to a stored procedure).

Consider this hypothetical alternative:

INSERT INTO ATable(val1, val2, val3, ID, val4)
SELECT c.x1, c.x2, c.x3, b.id, c.x4
FROM BTable b
JOIN Ctable c on b.id = c.id
WHERE b.Text = ['Your Text Here']

Obviously x1 through x4 aren't taken from table B in this case. In
Utahduck's example, x1 through x4 aren't taken from /any/ table.

[Back to original message]