Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

INSERT INTO joint problem

Posted on 2000-04-02
9
Medium Priority
?
1,001 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 400 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

705 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