Re: Replacing data in 1 table from another

[Patrick Shoaf]

I tired both statements you suggested.  The update is what I would like to 
use, but I got an error, ERROR 1064: You have an error in your SQL syntax 
near ' sdtik set sales.sales ....'

I used the REPLACE command, but it had the effect of adding records for 
customers not in the statistics file.

Any suggestions on how to correct the UPDATE statement?

Patrick

At 05:14 PM 6/16/2003, Roman Neuhauser wrote:
# [EMAIL PROTECTED] / 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
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message.    see http://www.eyrie.org./~eagle/faqs/questions.html


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]