Posted by dhek on 02/22/07 18:03

"Ed Murphy" <emurphy42@socal.rr.com> wrote in message
news:45ddcea3$0$27110$4c368faf@roadrunner.com...
> 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.

He maaaaan, I totally get it now and it works like a bloody charm. If I
could, I would award u guyz 1000000 points each - I really appreciate it -
thanks a lot.

Best Regards,
Daniel

[Back to original message]