473,728 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UPDATE table to a joined query...

Hello all,

This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An example of what I'm trying to do is below:

update (tbl_ind_manana s LEFT JOIN (select count(*) as count, (dubicacion ||
zona || manzana) as cod_manzana from tbl_censo_pobla cion_1993 group by
dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as tbl2 set
poblacion = count;

Basically I have a table (tbl_ind_manzan as) with a unique code (relacion) that
can be linked to a field in the query result (cod_manzana). I want to update
a field in the table with the count(*) result in the query. The update
statement (as I have attempted it above) doesn't work...it seems that I can do
nothing but directly update the values in a table. As soon as I try to supply
anything other than just a table to the update statement, it doesn't like it.

I'm sure others have experience with this issue...it seems to me that there
would be many cases where such an approach would be useful. I'm hoping
there's another method that I might be able to use that could accomplish
essentially the same result. Any suggestions are greatly appreciated.

Kind regards,
Mike
----------------------------------------
This mail sent through www.mywaterloo.ca

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #1
2 8536
On Wed, Oct 15, 2003 at 22:39:39 -0400,
Mike Leahy <mg*****@fes.uw aterloo.ca> wrote:
Hello all,

This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An example of what I'm trying to do is below:

update (tbl_ind_manana s LEFT JOIN (select count(*) as count, (dubicacion ||
zona || manzana) as cod_manzana from tbl_censo_pobla cion_1993 group by
dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as tbl2 set
poblacion = count;

Basically I have a table (tbl_ind_manzan as) with a unique code (relacion) that
can be linked to a field in the query result (cod_manzana). I want to update
a field in the table with the count(*) result in the query. The update
statement (as I have attempted it above) doesn't work...it seems that I can do
nothing but directly update the values in a table. As soon as I try to supply
anything other than just a table to the update statement, it doesn't like it.

I'm sure others have experience with this issue...it seems to me that there
would be many cases where such an approach would be useful. I'm hoping
there's another method that I might be able to use that could accomplish
essentially the same result. Any suggestions are greatly appreciated.


A similar question was asked in the last week.

You want to do the join in the from item list and than join the table
being updated to the join from the from item list in the where clause.

Something like:
update a set a.count = c.count from (select a left join b) as c
where a.id = c.id;

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2
You appear to have accidentally just replied to me.

On Thu, Oct 16, 2003 at 12:48:35 -0400,
Mike Leahy <mg*****@fes.uw aterloo.ca> wrote:
Okay...that seems to work. However, there are two things that are causing
me problems. First, if I use the statement exactly as you have it in your
reply I get 'ERROR: parser: parse error at or near "." at character 15' -
it doesn't seem to like the 'a.' before the first reference to the column
being named. It's clearly nothing serious - I suppose it's redundant to put
it there anyway, since we've already indicated we're updating the columns in
table 'a' to begin with.
That was my mistake. A table name there makes no sense since as you noted
the column has to be in the table being updated.
Second, update I'm trying to run is about 20000 records (both in the table
being updated, and in the joined query). When I run this update, there is
the usual length of time to run the query itself, then when it begins
updating my processor sits at 100% for about 15 minutes (with a 2.4 GHz
processor). Is this normal, or is my update statement structured poorly
somehow? I tried dumping the query to a table rather than executing it
within the update statement, and that didn't make much difference (example
below).
An explain analyze would probably be useful to see.
update tbl_ind_manzana s set poblacion = c.thecount from (select cod_manzana,
thecount from tbl_ind_manzana s left join tmp_query on
tbl_ind_manzana s.cod_manzana = tmp_query.cod_m anz) as c where
tbl_ind_manzana s.cod_manzana = c.cod_manzana;

Can anyone suggest how this might possibly be improved so that it isn't so
computationally intensive?
You probably want want to create an index on tmp_query (cod_manz) (and of
course (tbl_ind_manzan as (cod_manzana)) so that you don't need to do a sort
or nestloop to do the left join.

At any rate...it seems to work fine despite the length of time to execute.
Thanks for the help Bruno.

Mike
-----Original Message-----
From: Bruno Wolff III [mailto:br***@wo lff.to]
Sent: October 16, 2003 10:30 AM
To: Mike Leahy
Cc: pg***********@p ostgresql.org
Subject: Re: [GENERAL] UPDATE table to a joined query...

On Wed, Oct 15, 2003 at 22:39:39 -0400,
Mike Leahy <mg*****@fes.uw aterloo.ca> wrote:
Hello all,

This question is related to updating tables - is there any way to

calculate or
update the values in a column in a table to the values in a field produced

by
a query result? An example of what I'm trying to do is below:

update (tbl_ind_manana s LEFT JOIN (select count(*) as count, (dubicacion

||
zona || manzana) as cod_manzana from tbl_censo_pobla cion_1993 group by
dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as tbl2 set
poblacion = count;

Basically I have a table (tbl_ind_manzan as) with a unique code (relacion)

that
can be linked to a field in the query result (cod_manzana). I want to

update
a field in the table with the count(*) result in the query. The update
statement (as I have attempted it above) doesn't work...it seems that I

can do
nothing but directly update the values in a table. As soon as I try to

supply
anything other than just a table to the update statement, it doesn't like

it.

I'm sure others have experience with this issue...it seems to me that

there
would be many cases where such an approach would be useful. I'm hoping
there's another method that I might be able to use that could accomplish
essentially the same result. Any suggestions are greatly appreciated.


A similar question was asked in the last week.

You want to do the join in the from item list and than join the table
being updated to the join from the from item list in the where clause.

Something like:
update a set a.count = c.count from (select a left join b) as c
where a.id = c.id;


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
595
by: sqlgoogle | last post by:
Hi I'm having update problem. Here is the senario I have to different db server (SQL Server) linked with each other In DB Server 1 I have 2 tables & In DB Server 2 I have 3 tables. I have joined tables with each other first & then between the servers When I run select on DB Server1 with both tables joined I'm getting more values then when I run the select between the DB Servers (about 20
2
7495
by: Fons Roelandt | last post by:
Heelo, I have to Update all fields from a table with the values of a related table, i've tried some querys i found on the internet, but nothing seems to word, i even tried to lookup the value using dlookup, but even that doesnt seem to word in a update query. The query that i think should work is this one: UPDATE tblOrderLines AS tblO
4
3399
by: John Baker | last post by:
Hi: I have two tables, a setup table (TblSetup) and a purchase order table (tblPO). When i construct a query with ONLY the tblPO shown, and a type in parameter for the PO number, I an update the original tblPO. However, when I introduce the setup file into the query I cannot update the result. THis is true if I make a link to Setup or not. In fact, setup contains the client ID, which i wish to test against the client ID in the PO...
4
11336
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 AddressDescription of Entity 456 = AddressDescription of Entity_ID 123 Address1 of Entity 456 = Address1 of Entity_ID 123 City of Entity 456 = City of Entity_ID 123
2
535
by: NigelMThomas | last post by:
I have an especially challenging problem. I know there are a few geniuses in this group so perhaps; you can advise me whether or not this can be done as an update query in Access. Thanks. I am trying to join two tables if certain conditions are met § TaxID in Table 1 must match TaxID in Table 2 § DRG in Table 1 must match DRG in Table 2 § BeginDate in Table 1 must match BeginDate in Table 2 § If all these criteria are met and...
1
1566
by: Foef | last post by:
When I have a stored procedure, with multiple tables in MS Access, in a dataset and I change it in my DataGrid, I get the next message when I want to update my DataSet: InvalidOperationException: "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information." I tried to use an update query but when I fill my DataTable using myDataAdapter.Fill(myDataSet, myTable)
3
8748
by: turtle | last post by:
I have Two tables (Table1 and Table2). Both tables have a common field called part number. Table 1 contains an extra field that i would like to update table 2 to match if the part number matches. I created a join between Table1 and Table 2 but couldn't update Table 2 since the recordset was unupdateable. What i would like: Table 1 Part Number Field 2
1
15760
by: racquetballer | last post by:
I need to to an update query that involves three tables: table Dealer needs to be updated with data from table Personnel and table Title. Dealer is joined to Personnel where Dealer.Dealer_Code = Personnel.so_cd Personnel is linked to Title by Personnel.name_id = Title.name_id I need to update fields Principal_First_Name, Principal_Last_Name, and Company_Email in Dealer from FirstName, LastName, and InternetEmailAddr in Personnel where...
2
5658
by: joeyrhyulz | last post by:
Hi, I'm trying to make a very simple update statement (in Oracle) in jet sql that seems much more difficult than it should be. The root of my problem is that I'm trying to update a field on a table using dmax, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dmax function, the code/ms access seems to ignore the joins. As a result, all payees...
0
8891
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9263
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9121
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8110
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6704
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4522
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4787
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2642
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2159
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.