Solved

INSERT INTO joint problem

Posted on 2000-04-02
9
986 Views
Last Modified: 2008-03-17
I'm fairly new to db design, therefore this questions:

I have (among others) three tables:

tblProjects
  (ProjectID, Name, etc.)
tblTeamMembersxProjects (joint table)
  (ProjectID, TeamMemberID)
tblTeamMembers
  (TeamMemberID, FirstName, LastName, etc.)

Now, I have an asp page where one can add/edit project information. That page has a select list included that shows ALL possible TeamMembers pulled from tblTeamMembers (if it's an update then the TeamMembers associated with the project are already highlighted). Right now, if one enters a NEW project and selects some TeamMembers on that list I am running two separate conn.execute(sql) commands: one for the insertion of the project and the other to insert the TeamMembers from the list into the joint table.

This solution sounds like a fairly dirty solution to me. Is it possible to utilize the JOIN command in INSERT & UPDATE commands...???

I tried to find info on other sites (such as sql-zone.com or informit.com) but have not found anything concerning that specific question...

Thanx a bundle!
0
Comment
Question by:gsn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 

Expert Comment

by:chsoh
ID: 2678790
I still not understand what is

Is it possible to utilize the JOIN command in INSERT & UPDATE commands?

you mean you want to INSERT & UPDATE perform is single SQL statement ? From your question, are you facing problem when ADD a new projects only ? or Add & UPDATE projects ??
0
 
LVL 1

Author Comment

by:gsn
ID: 2678806
yes, I want to INSERT & UPDATE in a single SQL statement, IF POSSIBLE... As I said: right now I am executing two separate sql statements (for both cases: INSERT and UPDATE!)
0
 

Expert Comment

by:chsoh
ID: 2678846
The INSERT, UPDATE & DELETE statement can only be execute for a single table. so there is no way to do the statement like

INSERT into projects, TeamMembersxProjects
VALUES .......

only SELECT statement can do the query on multiple tables like

SELECT * from Projects A, TeamMembersxProjects B, TeamMembers C
where A.projectID = "11111"
AND A.projectID = B.projectID
and B.TeamMemberID = C.TeamMemberID
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:srikant033100
ID: 2679148
if u want to execute any mutiple query and DML operation along with some validation then u can go for procedure or functions depending on your requirement where u can even do some validation like a program. i think this will solve ur problem of else give me exactly what u want with an example so that i will try to solve the problem back with an example.

Bye

Srikant
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 2682948
Insert and Update statements will always only insert/update rows in 1 table, but the inserted/new values may come from a join.
INSERT yourtable (columns)
SELECT columns
FROM Table1 JOIN Table2 ON condition
UPDATE yourtable
SET column1 = value1, column2= value2...
FROM yourtable JOIN othertable ON condition

Is this what you need ?
0
 
LVL 1

Author Comment

by:gsn
ID: 2719812
angelIII brought me on the right track... sorry it took so long...
0
 
LVL 1

Author Comment

by:gsn
ID: 2719815
That were an easy 100 points - I appreciate your help!
0
 
LVL 4

Expert Comment

by:srikant033100
ID: 2743192
Hi

I don't understand on what ground u have rejected my answer. The question what u gave what that u want to insert or update some records based on certain condition or criteria. The procedure or function is the best possible way. Even what Mr. qsn gave was same and the if we write it in a procedure that would be much more helpfull So can u give me the ground on which u have rejected the answer.
0
 

Expert Comment

by:chsoh
ID: 2743226
I don't understand on what ground u have accepted angelIII answer.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question