473,387 Members | 1,572 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Update Foriegn Key

38
I have two tables, Company and Opportunity. Company table has two columns CompanyID (autonumber) primary key and Company.

Opportunity table has OpportunityID (autonumber) primary key CompanyID , Industry and Contact. The relationship between the two tables is a one to many (1Company to Many Opportunities). I appended new records to the company and opportunity table. The problem is i have is updating the opprotunity table such that i will have the CompanyID field of the Opprotunity table reflect the numbers of the primary key CompanyID from the Company table.


When I run the following code:

UPDATE Opportunity AS A SET A.[Company ID] =
( SELECT B.COMPANYID
FROM COMPANY B
WHERE B.COMPANY = A.COMPANY);

I get the an error that says 'Operation must use an updateable query'.

Any help would be great.

-Dev1
Mar 28 '08 #1
1 1018
Dev1
38
HELP! Is the question that I have to vauge?


I have two tables, Company and Opportunity. Company table has two columns CompanyID (autonumber) primary key and Company.

Opportunity table has OpportunityID (autonumber) primary key CompanyID , Industry and Contact. The relationship between the two tables is a one to many (1Company to Many Opportunities). I appended new records to the company and opportunity table. The problem is i have is updating the opprotunity table such that i will have the CompanyID field of the Opprotunity table reflect the numbers of the primary key CompanyID from the Company table.


When I run the following code:

UPDATE Opportunity AS A SET A.[Company ID] =
( SELECT B.COMPANYID
FROM COMPANY B
WHERE B.COMPANY = A.COMPANY);

I get the an error that says 'Operation must use an updateable query'.

Any help would be great.

-Dev1
Mar 28 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Girish Agarwal | last post by:
--0-474210375-1058976151=:31789 Content-Type: text/plain; charset=us-ascii Content-Id: Content-Disposition: inline Note: forwarded message attached. __________________________________
0
by: Fraser Hanson | last post by:
Hello, I have a table which has a foreign key relationship with itself. I want and expect my updates to cascade (deletes definitely cascade as expected) but instead I just get error 1217:...
2
by: Gunnar Vøyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
16
by: akej via SQLMonster.com | last post by:
Suppose i have table with 20 columns. My store proc takes two input parameters (param1, param2), now i need to UPDATE column with param2 according to param1: e.g if param1 = 222 --> UPDATE...
0
by: Robert Fitzpatrick | last post by:
I have tblhud74b that has a foreign key to tblhudunits with Restrict Updates. I have a function that DELETEs corresponding records before doing an UPDATE right after the DELETE statement and...
4
by: irfi | last post by:
Hi, I am a form which has a sub form, The sub form is linked with primary key of main form as a foriegn key in sub form, very basic, In the main form, I have another field which is...
1
by: James Hallam | last post by:
I have a parent record with a child record. The parent record contains structures and the child records contains visits to those structures. Each table has a field named "Complete", what I...
1
by: Kushwaha | last post by:
Hi, I want to update a field in primary table using a single trigger when I using a foriegn table at the time of inserting a value, deleting a value or modifying that value.
2
by: raghunana | last post by:
hello friends, i am a beginer.I was unable to add a foriegn key constraint to my NAMES table which refers my EMPLOYEE TABLE SQL> desc employee Name Null? ...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
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...
0
marktang
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,...
0
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 using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.