Get help on databases - MySQL, Oracle, Access, etc.
doo_much
Registered User
Posts: 26022 Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:
Post
by doo_much » 27 May 2011, 18:38
Should be easy enough.
Trying to insert ID's from one table into another using the name of their ID photo as a reference. Two tables in an Access Database and I'm running the SQL from MSQuery. Which is all I've got available in this environment.
Thought this should work but I'm obviously messing up somewhere.
Code: Select all
Insert into CARD_DATA.ID
select CRDHLD.crdhld_ID
from CRDHLD
where CARD_DATA.CM_PHOTO = CRDHLD.photo
Any help please?
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
Tribble
Registered User
Posts: 88465 Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:
Post
by Tribble » 27 May 2011, 18:56
PM RRF - he has been helping me. Jamin has also been very helpful. I think you need the table name in the insert statement - but what do I know?!
This is what I found:
INSERT INTO tblB(Field1, Field2,...)
SELECT Field1, Field2,...
FROM tbla
RuadRauFlessa
Registered User
Posts: 20576 Joined: 19 Sep 2003, 02:00
Location: Bloodbank
Post
by RuadRauFlessa » 28 May 2011, 14:24
Correctamundo Tribble....
The general form of an insert statement is as follows
Code: Select all
INSERT INTO [TABLENAME]
[(COL1[,COL2[,COL3[...]]])]
VALUES
[(VALUE1[,VALUE2[,VALUE3[...]]])]
So what you are looking for is this
Code: Select all
INSERT INTO [CARD_DATA]
([CARD_DATA].[ID])
VALUES (
(SELECT [CRDHLD].[CRDHLD_ID] FROM [CRDHLD])
)
WHERE [CARD_DATA].[CM_PHOTO] = [CRDHLD].[PHOTO]
Do note that depending on the sub-query it might not allow you to use sub-queries in insert statements.
Spoiler (show ) Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Tribble
Registered User
Posts: 88465 Joined: 08 Feb 2007, 02:00
Processor: Intel Core i7-4770K CPU@3.50GHz
Motherboard: ACPI x64-based PC
Graphics card: GeForce GTX 780 Ti
Memory: 16GB
Location: Not here
Contact:
Post
by Tribble » 28 May 2011, 14:44
Told you he could help you
hamin_aus
Forum Moderator
Posts: 18363 Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:
Post
by hamin_aus » 30 May 2011, 05:12
Code: Select all
Insert into CARD_DATA (ID)
select CRDHLD.crdhld_ID
from CRDHLD
where CARD_DATA.CM_PHOTO = CRDHLD.photo
I personally don't use VALUES in my insert statements unless I'm putting in values manually... personal preference, and so I have been told, not ideal
The sub query should be fine, but if it moans, use select into instead
Code: Select all
SELECT CRDHLD.crdhld_ID
INTO CARD_DATA.ID
FROM CRDHLD
WHERE CARD_DATA.CM_PHOTO = CRDHLD.photo
doo_much
Registered User
Posts: 26022 Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:
Post
by doo_much » 31 May 2011, 11:15
Sorted thanks - client did it themselves...
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey