Solved

INSERT INTO joint problem

Posted on 2000-04-02
9
983 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Read about achieving the basic levels of HRIS security in the workplace.
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…

740 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