CODEWITHSUNDEEP
mysql
ktm5124
ktm5124

Reputation: 12123

Is there a way to do this in MySQL?

I realize that what's below doesn't work:

set @T := (select A, group_concat(B) from X inner join Z on ...);

update T1 inner join @T as T2 on T1.A=T2.A
set ...

But conceptually it's what I want to do. I'll be using the result set @T quite a bit in my SQL file, and to keep the file trim I'd like to save it to macro rather than type it out in each query.

Is there a way to do the above? I've looked into stored functions and procedures as well as cursors, but haven't found a nice solution.

Upvotes: 0

Views: 50

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562911

The comments gave a good suggestion to use a temp table. I'll post an example and mark my answer as CW. 

CREATE TEMPORARY TABLE TempT AS SELECT A, GROUP_CONCAT(B) FROM X INNER JOIN Z ON ...;

UPDATE T1 INNER JOIN TempT AS T2 ON T1.A=T2.A
SET ...

Not that a temporary table is scoped to the current session. That is, it is visible only to the session that created it, and it is automatically dropped when your session ends.

If you want this table to be persistent and visible to multiple sessions, you can't use the TEMPORARY option. Just create the table as a normal table. This is often called a summary table when you store the result of an aggregate.

Then you have some design decisions to make about how frequently you truncate it and repopulate it with current data. That's up to your judgment -- there's no right answer, and it's one of the hardest problems in computer science to solve.

If you want the convenience of using the temp table, but you need it to be 100% in sync with other data, without worrying about how often you update it, you could consider defining a VIEW.

CREATE OR REPLACE VIEW TempT AS SELECT A, GROUP_CONCAT(B) FROM X INNER JOIN Z ON ...;

Upvotes: 1

Related Questions