Hi,
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 6 18223
On Feb 22, 8:57 am, "dhek" <d...@REMOVEvip.cybercity.dkwrote:
Hi,
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']
>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?
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.
"Ed Murphy" <em*******@socal.rr.comwrote in message
news:45***********************@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
On Feb 22, 10:06 am, Ed Murphy <emurph...@socal.rr.comwrote:
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!
<Ut******@hotmail.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...
On Feb 22, 10:06 am, Ed Murphy <emurph...@socal.rr.comwrote:
>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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Stephen Patten |
last post by:
Hi All,
While in the process of building my table (40 or so Insert statments)
can I then query ("select * from @Table_variable") and use the results
up to theat point for another insert into...
|
by: soni29 |
last post by:
hi,
i have a small question regarding sql, there are two tables that i
need to work with on this, one has fields like:
Table1:
(id, name, street, city, zip, phone, fax, etc...) about 20 more...
|
by: avinash |
last post by:
hi my self avi
i want to copy data from one table to other table,by giving certain
condition and i want o use insert statement .in this i want to pass some
value directly and some value from...
|
by: serge |
last post by:
/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an...
|
by: SueB |
last post by:
I currently have a 'mail-merge' process in my Access db project. It
generates custom filled out Award Certificates based on an SQL SELECT
statement in a VBA routine invoked by clicking on a...
|
by: Steve |
last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement
I got ASP error number 13 when I use the SELECT...FOR UPDATE statement
as below.
However, if I use SELECT statement without...
|
by: Cindy H |
last post by:
Hi
I'm having a problem getting the insert statement correct for an Access
table I'm using.
The Access table uses an autonumber for the primary key.
I have tried this:
INSERT INTO Tournaments...
|
by: rhaazy |
last post by:
Using MS SQL 2000
I have a stored procedure that processes an XML file generated from an
Audit program. The XML looks somewhat like this:
<ComputerScan>
<scanheader>...
|
by: nano2k |
last post by:
Hi
Shortly, I keep invoices in a table.
Occasionally, someone will fire the execution of a stored procedure
(SP) that performs several UPDATEs against (potentially) all invoices
OLDER than a...
|
by: flexsingh |
last post by:
Hello there I have kinda got gotten myself into a sticky situation. I am trying to do something which seams too big to do in my head but I feel I kinda know how to do it.
My problem is I have a...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |