# psh...@midmon.com / 2003-06-16 15:15:31 -0400:
I have two tables...
Table 1 newdata contains a lot of statistical data on our customers,
including last 12months sales amt.
table 2 sdtik contains all ticket information for all customers.
Both tables have acctno as primary key.
I can use:
SELECT sum(if(voidreason>0,0,amt)) as sales FROM sdtik where acctno=1234
and datein>=20020615
to gather customers' sales totals. How can I setup a single query where
the newdata.sales would be replaced with the summarized data from the
select.
you need UPDATE or REPLACE, together with GROUP BY
http://www.mysql.com/doc/en/REPLACE.html
http://www.mysql.com/doc/en/UPDATE.html
http://www.mysql.com/doc/en/SELECT.html
REPLACE sales (acctno, sales)
SELECT acctno, SUM(IF(voidreason > 0, 0, amt))
FROM sdtik
GROUP BY acctno
or
UPDATE sales, sdtik
SET sales.sales = SUM(IF(voidreason > 0, 0, sdtik.amt))
WHERE sales.acctno = sdtik.acctno
6 messages in com.mysql.lists.mysqlRe: Replacing data in 1 table from an...