View previous topic :: View next topic
|
Author |
Message |
Hari Kumar
New User
Joined: 28 Jul 2005 Posts: 19
|
|
|
|
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 |
|
|
ANIKET
New User
Joined: 28 Apr 2005 Posts: 11 Location: PUNE
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
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 |
|
|
sairam
New User
Joined: 30 Aug 2005 Posts: 58 Location: Kolkata
|
|
|
|
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 |
|
|
ANIKET
New User
Joined: 28 Apr 2005 Posts: 11 Location: PUNE
|
|
|
|
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 |
|
|
sairam
New User
Joined: 30 Aug 2005 Posts: 58 Location: Kolkata
|
|
|
|
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 |
|
|
ANIKET
New User
Joined: 28 Apr 2005 Posts: 11 Location: PUNE
|
|
|
|
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 |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005 Posts: 677 Location: NJ
|
|
|
|
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 |
|
|
nikyojin
New User
Joined: 05 Oct 2005 Posts: 94
|
|
|
|
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 |
|
|
ANIKET
New User
Joined: 28 Apr 2005 Posts: 11 Location: PUNE
|
|
|
|
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 |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
I dont think that u can update a view which have any join.
Correct me if i m wrong |
|
Back to top |
|
|
ANIKET
New User
Joined: 28 Apr 2005 Posts: 11 Location: PUNE
|
|
|
|
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 |
|
|
|