Posted by dhek on 02/23/07 06:38

<Utahduck@hotmail.com> wrote in message
news:1172172266.057991.105090@a75g2000cwd.googlegroups.com...
> On Feb 22, 10:06 am, Ed Murphy <emurph...@socal.rr.com> wrote:
>> 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.
>
> This is correct. You don't need to "select" from any table. You can
> even do things like:
>
> SELECT GetDate() -- Get the date... no tables involved at all
> SELECT 'I got this from ATable', * FROM ATable -- I do this quite
> often when merging several tables into one so I know the source
> SELECT 2+2 -- Just in case you forget what that comes to. :D
> SELECT 'Hello World!' -- I do this quite often as a form of
> troubleshooting, thought it more closely resembles SELECT 'Finished
> Step #7'
>
> Hope that helps!

It all help indeed of my lacking understanding of what is possible and what
is not. This clearifies a great deal and makes my life much easier. I'm no
longer a troubled man:-)

Thanks again for all your help - I really appreciate it.

Best Regards,
Daniel

[Back to original message]