Update + group concat (MySQL)

Hoe gebruik je group_concat icm. update? Twee methodes die ik gebruikte:

• Defineer de tabel ahb. de gegenereerde data. Dus zoiets als drop table xxx if exists gevolgd door create table xxx select ...
• Gebruik een tijdelijke tabel.

Beide methodes hebben nadelen. In mei 2019 vond ik eindelijk een betere oplossing.

Waarom?

De twee methodes die ik hierboven noem, hebben een paar problemen:

• Impliciete tabel-definities: Ik gebruik liever geen impliciete tabeldefinities, zoals je hebt bij create table xxx select ...: Ik wil graag meer controle, dus een expliciete definitie
• Insert vs. update: Ik vind insert niet prettig werken, omdat je daarmee twee keer alle velden moet benoemen. Da's complex en gaat gemakkelijk fout + dat het code genereert die moeilijk te onderhouden is. Daarom liever de records initialiseren met bv. maar één veld (typisch de pk) en daarna update gebruiken voor het verdere vullen. Daarbij kan ik per veld of per handjevol velden een apart commando gebruiken. Dat maakt de code veel eenvoudiger en gemakkelijker te onderhouden
• CPU-inefficiënt: Bij gebruik van een tussentabel, wordt de tabel eigenlijk twee keer gegenereerd. Sois: Performance is zelden een issue voor mij
• Code-inefficiënt: Bij gebruik van een tussentabel, moet ik een flink deel van de code dupliceren. Voor eindtabellen met veel velden, is dat een secuur en foutgevoelig klusje. Dus liever niet.

Mbv. een tijdelijke tabel

Voorbeelden dwh:

• sq_prepare_brands_per_sku_combined
• sq_prepare_orgbrush_combined.

sq_prepare_brands_per_sku_combined:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sq_prepare_brands_per_sku_combined`()
BEGIN

# Create table "brands_per_sku_tmp"
#############################################################################################
#
# * Main input: Table "sq_root_tmp" & "brush_tool_org_201808"

#############################################################################################
# Initialise table "brands_per_sku_tmp" with "sku"
#############################################################################################
#
drop table if exists brands_per_sku_tmp;

create table brands_per_sku_tmp
(
    sku		varchar(40)	primary key,
    sku_oem	varchar(40),
    brands	text
);

insert into brands_per_sku_tmp (sku, sku_oem)
select sku, sku_oem from root_tmp;


#############################################################################################
#  Select brands per sku → Temporary table "brands_per_sku_tmp2"
#############################################################################################
#
# Reason for using this temporary temporary table: I don't know how to change the code below
# to update an existing table, rather than creating a new one + don't want to spend too much
# time on it (May 2019)
#
# Test
#####################
#
# select distinct
#
#    root_tmp.sku,
#    root_tmp.sku_oem,
#    group_concat(distinct tool_brand separator ', ') as brands
#
# from root_tmp
# join brush_tool_org_201808 on root_tmp.sku_oem = brush_tool_org_201808.sku
#
# group by root_tmp.sku;

# Real
#####################
#
drop table if exists brands_per_sku_tmp2;

create table brands_per_sku_tmp2

select distinct

    root_tmp.sku,
    # root_tmp.sku_oem,
    group_concat(distinct tool_brand separator ', ') as brands

from root_tmp
join brush_tool_org_201808 on root_tmp.sku_oem = brush_tool_org_201808.sku

group by root_tmp.sku;


#############################################################################################
#  Update "brands_per_sku_tmp" with "brands_per_sku_tmp2"
#############################################################################################
#
update brands_per_sku_tmp
join brands_per_sku_tmp2 on brands_per_sku_tmp.sku = brands_per_sku_tmp2.sku
set
	brands_per_sku_tmp.brands	=	brands_per_sku_tmp2.brands;

END

In één keer

Complete testscript (kbo 3.0 » 200 - Complete procedure » 040.sql - 28 mei 2019). De truuk: Binnen de subquery een where-clause toepassen!

#
# KBO 3.0: Figure out group_concat + update
#####################################################################################################################
#
#####################################################################################################################
# Casus
#####################################################################################################################
#
# * Tabel root_tmp: Kolom "sku"
# * Tabel tool_tmp: Kolom "sku" & kolom "tool"
#
# * Tabel "output_tmp" met kolommen "sku" & "tools" met daarin alle tools (comma+spatie-separated)
#
use dwh;

#####################################################################################################################
# Initialise test tables
#####################################################################################################################
#
# tools_tmp
###################################
#
# drop table if exists tools_tmp;
# create table tools_tmp
# (
#     sku	varchar(40),   # Géén pk!
#     tool	text
# );

#insert into tools_tmp select sku, tool_main_id from brush_tool_org_201808;

# Output_tmp
###################################
#
drop table if exists output_tmp;
create table output_tmp
(
    sku		varchar(40)   primary key,   # Wél pk
    tools	text
);

# This is the trick: I want ot use update, as the output table is already initialised
#####################################################################################
#
insert into output_tmp (sku)
select sku from root_tmp;


#####################################################################################################################
# Doen!
#####################################################################################################################
#
update output_tmp
set tools =
(
    select group_concat(distinct tools_tmp.tool separator ', ')
    from tools_tmp
    where tools_tmp.sku = output_tmp.sku
    group by output_tmp.sku
);


#####################################################################################################################
# Debug
#####################################################################################################################
#
select * from tools_tmp;
select * from output_tmp;

P.s.: Ik heb deze oplossing niet alsnog geïmplementeerd in sq_prepare_brands_per_sku_combined en sq_prepare_orgbrush_combined: Program today for today, and tomorrow for tomorrow.

Bronnen

• https://stackoverflow.com/questions/6099870/mysql-update-with-a-join-and-a-group-concat