MVSFORUMS.com :: View topic - Insert Multiple Rows

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

Insert Multiple Rows

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

psridhar
Beginner

Joined: 16 May 2004
Posts: 68
Topics: 26

Posted: Sun May 16, 2004 11:24 am Post subject: Insert Multiple Rows

Hi,

Table 1:

Code:

-------------------------------------------
| | Authorised | |
| Id | for Department | Date |
--------------------------------------- |---
|AAA | DEP1 | 2004-01-10 |
|AAA | DEP2 | 2004-01-12 |
|AAA | DEP3 | 2004-02-05 |
|AAA | DEP4 | 2003-10-03 |
|BBB | DEP2 | 2004-05-01 |
--------------------------------------------

My requirement is I have to give authority to BBB (Insert rows with Id as BBB) for all the derartments for which AAA is having authority. Date must be current date.

Expected Insertions

Code:

-------------------------------------------
| | Authorised | |
| Id | for Department | Date |
----------------------------------------|
|BBB | DEP1 | 2004-05-16 |
|BBB | DEP3 | 2004-05-16 |
|BBB | DEP4 | 2004-05-16 |
-----------------------------------------

Can anybody suggest me an SQL which satisfies my requirement.

The only way I see is fetch all the rows from table with Id = AAA. For each department, Check for a row in the table with Id = BBB. If row is not there, insert it. But for this I need a program. But what I need is a single SQL (with out any program......!)

thanks in advance
Sridhar

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

Posted: Sun May 16, 2004 12:03 pm Post subject:

Sridhar,

Try this sql

Code:

INSERT INTO TABLE (SELECT CHAR('BBB'),AUTH_DEPT,CURRENT DATE
FROM TABLE
WHERE AUTH_DEPT IN (SELECT AUTH_DEPT FROM TABLE
WHERE ID = 'AAA')
AND AUTH_DEPT NOT IN (SELECT AUTH_DEPT FROM TABLE
WHERE ID = 'BBB'))
;

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

psridhar
Beginner

Joined: 16 May 2004
Posts: 68
Topics: 26

Posted: Mon May 17, 2004 10:08 am Post subject:

Thanks alot Kosulu Sridhar

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum