Solved

INSERT INTO joint problem

Posted on 2000-04-02
9
990 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
Independent Software Vendors: 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 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