List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:July 7 1999 7:24am
Subject:Re: Update with a sum from another table
View as plain text  
On Di, 1999-07-06 16:16:08 -0400, Dave without lastname alias Para-dox wrote:

> I have a table with a primary key, INVNUM, and I have a table with two
> primary keys, INVNUM and ITMNUM. There are multiple records in the second
> table for each record in the first table. I want to take the sum() of a
> field in the second table for each unique INVNUM and stick it into the first
> table... for example

Example:

CREATE TABLE t1 (
 invnum INT NOT NULL,
 itmnum INT NOT NULL,
 qty INT,
 PRIMARY KEY (invnum, itmnum)
);
CREATE TABLE t2 (
 invnum INT NOT NULL,
 totqty INT,
 PRIMARY KEY (invnum)
);
INSERT INTO t1 VALUES
 (10000,100,250)
,(10000,200,10)
,(10000,300,450)
,(10000,400,50)
;

REPLACE INTO t2(invnum,totqty)
 SELECT invnum, SUM(qty)
 FROM   t1
 GROUP BY invnum
;

This SELECT is quite basic SQL (while REPLACE INTO is MySQL specific,
I think).

Do you know the "SQL Tutorial" by James Hoffman?  You can find it for
example at <URL: http://willcam.com/sql/sqltut.htm >.  It's a nice
start and later on I'd recommend a good textbook an SQL.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Update with a sum from another tablePara-dox7 Jul
  • Re: Update with a sum from another tableMartin Ramsch7 Jul
    • Re: Update with a sum from another tableMartin Ramsch7 Jul