473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Creating an Update query with an embedded select statement

8
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:
Expand|Select|Wrap|Line Numbers
  1. update table2 c
  2. set c.fy = 
  3. (Select a.fy from
  4. tab1 a,
  5. tab2 b,
  6. where a.group_key = b.group_key)
This prouduces a -811 error

I have also tried the following:
Expand|Select|Wrap|Line Numbers
  1. update tab2 c
  2. set c.fy = t.fy
  3. where
  4. (select a.fy from
  5. tab1 a,
  6. tab2 b
  7. where a.group_key = b.group_key) t
I receive error -104 illegal symbol t

I have also tried the following
Expand|Select|Wrap|Line Numbers
  1. update tab2 c
  2. set c.fy = t.fy
  3. from
  4. (select a.fy from
  5. tab1 a,
  6. tab2 b
  7. where a.group_key = b.group_key) t
  8. where
  9. 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
Feb 21 '14 #1
13 1545
Rabbit
12,516 Expert Mod 8TB
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.
Feb 21 '14 #2
GSW327
8
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.
Feb 21 '14 #3
Rabbit
12,516 Expert Mod 8TB
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.
Feb 21 '14 #4
GSW327
8
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.
Feb 25 '14 #5
Rabbit
12,516 Expert Mod 8TB
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.
Feb 25 '14 #6
GSW327
8
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 misunderstanding.
Feb 27 '14 #7
Rabbit
12,516 Expert Mod 8TB
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.
Feb 27 '14 #8
GSW327
8
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.
Feb 27 '14 #9
Rabbit
12,516 Expert Mod 8TB
Please post what you attempted so I can guide you where you went wrong.
Feb 27 '14 #10
GSW327
8
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
Feb 27 '14 #11
Rabbit
12,516 Expert Mod 8TB
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.
Feb 27 '14 #12
GSW327
8
update tab2 c
set c.fy =
(Select a.fy from
tab1 a,
tab2 b,
where a.group_key = c.group_key)

Like this?
Feb 28 '14 #13
GSW327
8
I think I figuredit out... Thanks for all your help.
Feb 28 '14 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Guy Hocking | last post by:
Hi there, I have a problem in my ASP/SQL Server application i am developing, i hope you guys can help. I have a ASP form with list boxes populated by SQL tables. When a user selects a value...
4
by: | last post by:
I'm a bit of a newby to creating update queries so I was hoping those more experienced could help with what should be a simple query. I do know to create backups and test on a test database first....
1
by: avinash | last post by:
hi myself avi i am developing one appliacaion in which i am using vb 6 as front end, adodb as database library and sql sever 7 as backend. i want to update one table for which i required data from...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
1
by: Mike Preston | last post by:
This isn't working: Update Table1 set Field1 = (Select Mid(TextVariable,10) from Table2 where (Table2.OtherVariable = "WhatIKnowIsThere") and (mid(TextVariable,1,9)="FIRSTNINE")) where...
1
by: wallic | last post by:
I am using the code below to update my "loctable". When I run just the select portion of the query, it returns the rows that are specified... but when I run the update query it says all "row(s)...
5
by: Chris Kennedy | last post by:
Hi all I'm running SQL Server 2005 Express Edition. One database One table called Sites Fields as follows: id (bigint, Identity, Primary Key) SiteName (varchar(50), allows nulls) Generation...
1
by: themightyrhino | last post by:
I'm writing a process to perform multiple updates on a main table from information stored in different component reports. As you can see from the below, I'm using a select query to base the update...
0
by: rajath26 | last post by:
I am retrieving 5 rows from a table in a DB2 database and while I issue the SELECT I wish to have the data come in this format: <Row1Col1>,<Row1Col2>,<Row1Col3>,<Row1Col4>,<Row1Col5>...
1
by: thomas msofe | last post by:
hello there; im trying to use the following piece of code but it doest work, UPDATE itermdesc SET qnty = ( SELECT qnty -1 FROM itermdesc WHERE itermcode =1001 ) WHERE itermcode =1001 it...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.