barker@ems.MN.ORG (Bob W. Barker) (08/11/87)
Ok, I'm relatively new to this SQL buiness but I thought I could figure this
one out. So far, no luck. I'm sure this is childs play to some of you.
Say I've got a Unify database that contains 2 record types - cusstat and
adjust. cusstat contains status info on customer accounts and adjust contains
adjustments to customers account balances as well as a flag field to indicate
that the adjustment has or hasn't been posted to cusstat. Both cusstat and
adjust contain fields with customer names. I'm trying to whip up an SQL that
will update the cusstat record of each customer that has corresponding
adjustment records where the posted field = N (for NO). I'd also like to
change the posted field to 'Y' after doing this but this isn't a real problem
so I won't address it here.
This SQL doesn't quite work:
update cusstat
set CS_balance =
select CS_balance + A_dollars from cusstat, adjust
where CS_custname = A_custname and A_posted = 'N'/
It updates the balances of all customers in cusstat. Now, I imagine I need
a 'where' clause to match the update (ie. update cusstat where CS_custname =
A_custname or something) but I can't for the life of me get the syntax right!
Any help/pointers/suggestions etc. would be appreciated. Thank you!
-Bob Barker
--------------------------------------------------------------------------------
Bob Barker UUCP: {ihnp4|rutgers|amdahl}!{dayton|meccts}!ems!barker
EMS/McGraw-Hill ATT: (612) 829-8200
9855 West 78th Street FAX: (612) 829-1839
Eden Prairie, MN 55344eric@rtech.UUCP (Eric Lundblad) (08/13/87)
From article <464@ems.MN.ORG>, by barker@ems.MN.ORG (Bob W. Barker): > I'm trying to whip up an SQL that > will update the cusstat record of each customer that has corresponding > adjustment records where the posted field = N (for NO). I'd also like to > change the posted field to 'Y' after doing this but this isn't a real problem > so I won't address it here. > > This SQL doesn't quite work: > > update cusstat > set CS_balance = > select CS_balance + A_dollars from cusstat, adjust > where CS_custname = A_custname and A_posted = 'N'/ > > It updates the balances of all customers in cusstat. I believe that you want to execute something like: update cusstat set CS_balance = (select CS_balance + A_dollars from adjust where CS_custname = A_custname and A_posted = 'N') The difference here is that cusstat is not referenced on the from list of the subselect. The cusstat fields that are used in the subselect (CS_balance and CS_custname) would be correlated values to the outer context (ie. the update statement). In other words, for each row in cusstat, the subselect is executed using the current correlated values. If the subselect returns zero rows, then the current cusstat row isn't updated and the process in continued with the next row. If the subselect returns one value, then you change the cusstat row and go on to the next. The $64 question is: what happens when the subselect returns more than one values? Does the DBMS generate an error? Use the first value? The last value? All of them? Note that I'm not an expert on the supported syntax for Informix or Unify (I can't remember which one was referred to in the original article). However, assuming a moderate level of orthoginality (something that SQL isn't noted for) the above query should work. eric P.S. Damn, how do you spell orthoginality and where's my dictionary? -- Eric Lundblad ucbvax!mtxinu!rtech!eric
larry@xanadu.uucp (Larry Rowe) (08/18/87)
In article <464@ems.MN.ORG> barker@ems.MN.ORG (Bob W. Barker) writes: > ..... > >This SQL doesn't quite work: > >update cusstat >set CS_balance = > select CS_balance + A_dollars from cusstat, adjust > where CS_custname = A_custname and A_posted = 'N'/ > >It updates the balances of all customers in cusstat. Now, I imagine I need >a 'where' clause to match the update (ie. update cusstat where CS_custname = >A_custname or something) but I can't for the life of me get the syntax right! SORRY FOLKS, this query is not expressable in SQL. the left-hand side of a set stmt in an update can be an expression involving constants and refereences to attributes in the same record. i believe this limitation is in the langauge to avoid something known as the ``halloween problem'' where the order of scanning the table to be updated can change the semantics of the query. this arises because System-R (the folks who brought you SQL) does direct update (i.e., when scanning the table to be updated, the value is updated in the data page). QUEL allows this query to be written, as follows: replace cusstat(CS_balance=CS_balance+adjust.A_dollars) where cusstat.CS_balance=adjust.A_custname and adjust.A_posted='N' you've hit on one of the reasons folks say that QUEL is a better query language than SQL. i expect that all SQL vendors will extend their products with a construct to handle this case. however, it may take awhile because the implementation consequences are serious. btw, INGRES uses deferred update so the semantics can't depend on the scan order. interesting point about the way you write queries. you use implicit tuple variables (i.e., CS_balance refers implicitly to the cusstat table) but then you encode a tuple variable into the attribute name (i.e., CS_). larry
garyp@cognos.uucp (Gary Puckering) (08/21/87)
In article <464@ems.MN.ORG> barker@ems.MN.ORG (Bob W. Barker) writes: >I'm trying to whip up an SQL that >will update the cusstat record of each customer that has corresponding >adjustment records where the posted field = N (for NO). I'd also like to >change the posted field to 'Y' after doing this but this isn't a real problem >so I won't address it here. > >This SQL doesn't quite work: > >update cusstat >set CS_balance = > select CS_balance + A_dollars from cusstat, adjust > where CS_custname = A_custname and A_posted = 'N'/ > >It updates the balances of all customers in cusstat. This problem came up in March. You are not the only one to fall into this trap. Basically, SQL is rather counter-intuitive for these kinds of problems. It is easier to express a solution from the point of view of the adjustment transactions as the "driver" rather than the customer as the "driver". But, unfortunately, SQL doesn't work like that. (Pity!) Here's a copy of a similar situation reported by Stu Heiss, and my response: |Article: 103 of comp.databases |>From: garyp@cognos.UUCP (Gary Puckering) |Newsgroups: comp.databases |Subject: Re: need help with sql problem (informix) |Keywords: SQL, Informix |Date: 25 Mar 87 15:10:39 GMT |Reply-To: garyp@smokey.UUCP (Gary Puckering) |Organization: Cognos Incorporated, Ottawa, Canada | |In article <276@jpusa1.UUCP> stu@jpusa1.UUCP (Stu Heiss) writes: |>I'm trying to do what should be a simple update and can't believe the |>abysmal performance. Maybe I'm missing something obvious. The scenario |>is a 3 table database (name,address,accounts) and I'm updating a batch |>from a fourth table. Here's the code to do the name: |> update name_table |> set name = (select name from change_table where |> name_table.id = change_table.id) |> where name_table.id in |> (select id from change_table); |>This seems overly complex when all I want to do is find the entries in |>the name_table whose id match those in the change_table and update the |>name column (field). | |Wow! This is scarey! I hope someone can come up with an elegant SQL |request for this. I can't think of one. However, I can offer a suggestion |and a comment: | |1. You *must* have an index on the id column of change_table. Otherwise, | a sequential scan of change_table will be required for every row | read from name_table. | |2. The strategy SQL has forced you into is a bad one. You should be | able to drive the update request from the change_table, rather than | from the name_table. For examplem, in QTP, our 4GL transaction | processor, you would code this request as: | | access change_table link to name_table | output name_table update | item name final name of change_table | go | | For this request to execute efficiently, there need only be an index | on id of name_table. Essentially, this request does a natural join of | change_table and name_table and then it updates only the name_table row. | | Unfortunately, I can't see any way to do this in interactive SQL. | |3. This above strategy could be accomplished within a program by using | cursor. For example: | | sql declare get_changes cursor for | select id.change_table name.change_table | into :xid :xname | from change_table, name_table | where id.change_table = id.name_table | | Now you can code a loop which fetches the appropriate id's and names | into host variables xid and xname. Within the loop you code an SQL | update request like: | | sql update name_table | set name = :xname | where id = :xid | | |Like I say, I hope someone else can come up with a decent solution which |can be coded in interactive SQL. If not, it just goes to show you that |SQL is a lousy excuse for a 4GL. It leads to counter-intuitive requests. -- Gary Puckering 3755 Riverside Dr. Cognos Incorporated Ottawa, Ontario {allegra,decvax,ihnp4,linus,pyramid} (613) 738-1440 CANADA K1G 3N3 !utzoo!dciem!nrcaer!cognos!garyp