Posted by Utahduck on 02/22/07 19:24

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!

[Back to original message]