Sub query on Update table

[Stopofeger]

Hi there,

 

I need to update field and set it from an another fields grouped avg. But can i use sub queries that produce multiple rows in SET?

example i need to select this

SELECT AVG(field1) from table1 Group BY field2

 

and update it into table1.field3

Is it possible, i know i can do it with php by first retreive it then update?

 

but for that i wud to make a query for each row.

what is an efficient solution to this?

[fenway]

I would question your decision to keep dynamic data stored in a table, unless you have a good reason to do so.

[artacus]

unless you have a good reason to do so.

 

That's true to a point. If you've got a small dataset, and it takes a fraction of a second to calculate avg then don't bother storing it. A good reason to do so would be when you have hundreds of thousands of records that you need to average.

 

I'm not entirely clear on what you are trying to do, but it sounds like you need a where clause so your subquery only returns a single row for the info you are looking for.

[fenway]

Actually, I would argue that it depends on how often the 100K records are being averaged... but that's just me... I hate storing redundant data.

[artacus]

I hate storing redundant data.

 

True. But the client and the end user could care less about what goes on on the back end. They just know that when given the choice between a page that loads in 11 seconds and one that loads in .4 seconds, they like the quicker one.

 

But I don't think you can categorically say that one method is right or wrong. I've had some projects where calculating aggregate data on the fly was the right solution, other projects where caching data structures in PHP was the solution, and projects where caching the info in a database was definitely the right answer.

[fenway]

Perhaps... I guess all my clients want real-time data.

[Stopofeger]

I would question your decision to keep dynamic data stored in a table, unless you have a good reason to do so.

Good question. But the client wants it that way.

Everyone's talking about why this shud be done. I want to know how it should be done.

Does the Update keyword accepts several row of data?

Same question goes for Insert keyword also.

 

[fenway]

Yes, you can do a multi-table update as of version 4.1+... I don't know what you mean for insert, except that you can write a multi-VALUE()'ed insert too.