Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Updating Two Tables in same query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Updating Two Tables in same query
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    Post subject:
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: 1452
Location: Chicago, IL

PostPosted: Mon Oct 17, 2005 6:07 pm    Post subject: Re: Updating Two Tables in same query
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Re: Updating Two Tables in same query
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: 680
Location: NJ

PostPosted: Wed Oct 26, 2005 6:39 pm    Post subject: update 2 tables
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    Post subject: Re: Updating Two Tables in same query
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    Post subject:
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: 1187
Location: Bangalore,India

PostPosted: Thu Oct 27, 2005 1:08 pm    Post subject:
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    Post subject: Re: Updating Two Tables in same query
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 1 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Updating the counters after eliminati... PANDU1 DFSORT/ICETOOL 12 Mon Nov 21, 2016 9:47 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us