Creating an Update query with an embedded select statement

[GSW327]

I have coded this so many ways I am now confused: here is what I would like to do but I cannot get the code to work:
Tables:
Tab1:
group_key
date
amount
agy
fy

Tab2:
group_key
description
address
fy - blank
agy - blank

I want the fy and agy from tab1 to be populated in tab 2 matching on Group_key from both tables. Below is the code I have tried and the errors I am receiving I hope you can help.

first try:

update table2 c
set c.fy = 
(Select a.fy from
tab1 a,
tab2 b,
where a.group_key = b.group_key)

This prouduces a -811 error

I have also tried the following:

update tab2 c
set c.fy = t.fy
where
(select a.fy from
tab1 a,
tab2 b
where a.group_key = b.group_key) t

I receive error -104 illegal symbol t

I have also tried the following

update tab2 c
set c.fy = t.fy
from
(select a.fy from
tab1 a,
tab2 b
where a.group_key = b.group_key) t
where
c.group_key = t.grup_key

and I receive the error stating from is an illegal keyword for the update clause.

I have tried several iteration for all of the above but I can not get it to work, I can write a cobol program to make it work but through my logic one of the above options should work with a little tweaking. I hope some one can help me!
Thanks in advance for your time!!!
Greg Walker

[Rabbit]

You are duplicating information that does not need to be duplicated. When you need that information, you can just join to the table to retrieve it, there's no need to store it twice. That would break the rules of normalization. Please read our article on normalization here: http://bytes.com/topic/access/insigh...ble-structures.

As for your attempts...

In your first attempt, your subquery in that position must only return one record. You are getting an error because your subquery is returning more that one record.

In your second attempt, you are trying to alias a subquery in a WHERE clause. Aliases are for record sets in the FROM clause or columns in the SELECT clause. They are not used in the WHERE clause. Aside from that, your subquery by itself in the WHERE clause doesn't do anything.

In your third attempt, DB2 does not allow a FROM clause in the UPDATE statement.

[GSW327]

There is a reason I am attempting this, it's not for look up purposes, can you help me with making the query work? We have a lot of duplicated data in our ADHOC system. I know why my data is the way it is, I am asking for help on a query not looking for criticisms on my data layout I did not create this monster I am trying to solve a space issue with out having to re write a system. I hope you can help with correcting my query and not just tell me what is wrong, DB2 is doing doing that. By the way, thank for giving the DB2 explanations of my query errors, I knew what was wrong I was just showing the attempts for a better understanding of what I was trying to do.

[Rabbit]

Your first attempt was the closest. Since the subquery should only return one row, the fix then is to have it return the one row that you need. That's not something I can answer for you because you haven't told us what row you need. You only describe the relationship between tab1 and tab2, you haven't told us how table2 is related to all this.

[GSW327]

Tab 2 and tab one are keyed and related to each other by Group_key I need to read every row in tab one and take the FY column/row for each record and add it to the FY column/row for each corresponding group_key. And I need to do the same for the FY. I hope you can help.

[Rabbit]

You still haven't told me how table2 is related to all this. You referenced 3 tables in your query: tab1, tab2, and table2. You told me about tab1 and tab2. You still need to tell me about table2.

[GSW327]

Ther are not three tables there are only 2 I may have mis typed but I need to update the FY and AGY in tab 2 from the values in tab one by matching the group key. I hope this is clearer, sorry for the confusion and misunderstandin g.

[Rabbit]

Then you need to take the first query, get rid of the subquery portion and join your tables on the group_key on line 1.

[GSW327]

Can you please help me with the code I have been attempting this for a while and can't get it to work correctly,
Thank you so much for being patient with me I truly appreciate it.

[Rabbit]

Please post what you attempted so I can guide you where you went wrong.

[GSW327]

OK can get the select down but I don't know how to incorporate the update in the select below:

Select a.fy , a.agy
from a60dev.tb_adg1 a,
a60dev.adg1 b
where
a.group_key = b.group_key

This is the closest I have come when I try to replace the select with update I can't code it correctly to have DB2 accept it.
Thanks again

[Rabbit]

UPDATE and SELECT statements are different, you can't simply write a SELECT and convert it to an UPDATE.

I forgot this was for DB2, forget what I said in post #8. What you need to do instead is take your first query, fix the table references, and then in your subquery, reference the outer taable to filter for the correct Group Key.

[GSW327]

update tab2 c
set c.fy =
(Select a.fy from
tab1 a,
tab2 b,
where a.group_key = c.group_key)

Like this?

[GSW327]

I think I figuredit out... Thanks for all your help.