IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Updating Two Tables in same query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Hari Kumar

New User


Joined: 28 Jul 2005
Posts: 19

PostPosted: Mon Oct 17, 2005 1:19 pm
Reply with quote

Hi All,


How to Reffer Two Tables in single UPDATE query.
Give some Examples.

Thanks in Advance,
Hari B

Don?t tell GOD how big your problems are, Tell ur problems how big your GOD is...?
Back to top
View user's profile Send private message
ANIKET

New User


Joined: 28 Apr 2005
Posts: 11
Location: PUNE

PostPosted: Mon Oct 17, 2005 5:49 pm
Reply with quote

hey Hari

I Don?t think that you can update two tables using single UPDATE query. I might be wrong but I never saw such kind of code. What I suggest is create a view for the tables you want to update and then update that view. Am I right expert guys?
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Mon Oct 17, 2005 6:07 pm
Reply with quote

Quote:
I might be wrong but I never saw such kind of code.


No you are not.... I agree with this....

Regards,

Priyesh.
Back to top
View user's profile Send private message
sairam

New User


Joined: 30 Aug 2005
Posts: 58
Location: Kolkata

PostPosted: Tue Oct 18, 2005 12:00 pm
Reply with quote

Exactly only one table can be updated but i think(not sure) we can use other tables for condition.If yes then please anyone give some sample code where other tables used in condition.
Back to top
View user's profile Send private message
ANIKET

New User


Joined: 28 Apr 2005
Posts: 11
Location: PUNE

PostPosted: Tue Oct 18, 2005 3:01 pm
Reply with quote

Hi Sairam,
I think you can use columns from more than two tables only in where clause like:

-------------------------------------------------------------------------

UPDATE TABLE-1
SET COL-1 = 'VALUE'
WHERE COL-2 = 'VALUE-2'
AND COL-3 =
(
SELECT B.COL-11 FROM TABLE-2 B
WHERE B.COL-22 = 'VALUE-4'
)

-------------------------------------------------------------------------

In this way you can refer more than two tables in where condition but the problem is that our friend Hari wants to update two tables using single update query. I think it is not possible unless he uses Views.
Back to top
View user's profile Send private message
sairam

New User


Joined: 30 Aug 2005
Posts: 58
Location: Kolkata

PostPosted: Tue Oct 18, 2005 3:52 pm
Reply with quote

Hi Aniket

will the following code work in DB2 ?
There is a error coming for 'from' clause.
I don't know 'from' is allowed or not,but query is perfect one


UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
Back to top
View user's profile Send private message
ANIKET

New User


Joined: 28 Apr 2005
Posts: 11
Location: PUNE

PostPosted: Tue Oct 18, 2005 8:18 pm
Reply with quote

Hi Sairam,

No, I don?t think that this query will work. Because the basic structure for UPDATE is like:

_____________________________________________________________
UPDATE <table_name>
SET <col_name>
WHERE <condition1, condition2??.>

_____________________________________________________________
So you can?t use ?FROM? in update query. (Exception is the sub-query, which I described, in my previous message). You can check this at IBM?s public library.
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 677
Location: NJ

PostPosted: Wed Oct 26, 2005 6:39 pm
Reply with quote

hi friend,
i had ref to many things and came to a conclusion that up to my knowlg there is no statement works for updating two tables in a single query.
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Thu Oct 27, 2005 10:13 am
Reply with quote

Hi Aniket,

It's quite true we cannot update two tables in a single query but...
Quote:
create a view for the tables you want to update and then update that view

The expression in Quotes above is very confusing.How can we update the individual Base tables by updating a View. Are views updateable.Acoording to my knowledge individual views are formed for selection of reuired columns from different tables.I've been trying to get this answer for a long time but I am not clear on this even after makin a lot of searches.
If possible I would like to get some information on the same.

Thanks,
Nikhil .S.
Back to top
View user's profile Send private message
ANIKET

New User


Joined: 28 Apr 2005
Posts: 11
Location: PUNE

PostPosted: Thu Oct 27, 2005 1:03 pm
Reply with quote

Hello Nikhil,
Here are the resolutions for your queries:

?How can we update the individual Base tables by updating a View?

suppose you have two tables, table-1 and table-2. You want to update col-1-1 and col-1-2 from table-1 and col-2-1 and col-2-2 from table-2. so first create view on these two tables for all concerning columns. Now use this view in UPDATE statement.

?Are views updateable?

why not. Here is the syntax for UPDATE:

UPDATE---- ( table-name/view-name)
SET -------
WHERE conditions


Hope this will help.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Thu Oct 27, 2005 1:08 pm
Reply with quote

I dont think that u can update a view which have any join.
Correct me if i m wrong
Back to top
View user's profile Send private message
ANIKET

New User


Joined: 28 Apr 2005
Posts: 11
Location: PUNE

PostPosted: Fri Nov 04, 2005 4:23 pm
Reply with quote

Hi GuptaE

Yes you are right. Thanks for correcting me. For any maintenance privileges like insert update and delete the sub select should not be ?Read Only? means the FROM clause must contain only one table name or view name. This means we cannot UPDATE, INSERT or DELETE a row on the view, which have a join in its sub select.

Thanks once again,
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Need to fetch data from so many DB2 t... DB2 9
Search our Forums:

Back to Top