mysql query help

[mccrack]

Greets peeps, need an assist with a MySQL query updating with the sum of 2 columns.

TABLE1=Name,Age
TABLE2=Name,Age

What i want to do is update TABLE1.Age withe the combined value of TABLE1.Age & TABLE2.Age but cant seem to get it right?

Thought it would have been something along the lines of:

update TABLE1 set Age= sum(TABLE1.Age + Table2.Age) where Name='x';

But that gives a:

ERROR 1111 (HY000): Invalid use of group function

 

[e11eveN]

The WHERE clause in this context just filters the result set based on 'Name' - you want a way to correlate the 'Age' entries in TABLE1 with the correct ones in TABLE2 based on 'Name'.

Maybe try something like:

update TABLE1 set Age = sum( TABLE1.Age + TABLE2.Age )
join TABLE2 on TABLE2.Name = TABLE1.Name

 

[mccrack]

Thanks for the assist e11eveN! Much appreciated.

But alas, in trying to simplify the issue as much as possible I have skewered the scenario.

I have a variable set in a script being passed down to the SQL Query and need to pull that into the update so SUM will need to be used?

######
DIFF=3;

update TABLE1 set Age= sum(TABLE1.Age + $DIFF) where Name='x';
######

 

[wishblade]

From my understanding of the last problem, wouldnt dropping the 'sum' keyword solve the problem?

Sent from my GT-I5500 using MyBroadband Android App

 

[edg3]

From my understanding of the last problem, wouldnt dropping the 'sum' keyword solve the problem?

It should do the trick, afaik sum() is used to create a sum of all the contents for all rows you use it on in a select

(SELECT Table1.Name as Name1, Table2.Name as Name2, Table1.Age + Table2.Age As NewAge FROM Table1, Table2
as opposed to
SELECT sum(Table1.Age) as Table1Sum, sum(Table2.Age) as Table2Sum FROM Table1, Table2)

As for the update, "SET Table1.Age=Table1.Age + $DIFF" should do the trick.

 

[wishblade]

As for the update, "SET Table1.Age=Table1.Age + $DIFF" should do the trick.

Pretty much what i was trying to get at...

 

[mccrack]

awesome stuff, dropping the sum did the trick

tx!