Records bijwerken (MySQL)

Als het gaat om het updaten van bestaande records, krijg je al snel te maken met het commando UPDATE. Maar er zijn meer mogelijkheden:

Als er al een relatie is tussen de twee tabellen

update tbl_powertools03
inner join tbl_powertools01 on tbl_powertools03.merk = tbl_powertools01.brand_uppercase
set tbl_powertools03.merk = tbl_powertools01.brand_normalcase

Zonder relatie

Hier wordt een tabel gevuld aan de hand van een andere tabel, zonder join. Additioneel voordeel: Je hoeft geen velden te benoemen die leeg blijven in de doel-tabel:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
WHERE conditions;

Van Select- naar Update-query

Dit is een situatie die ik regelmatig tegenkom: Ik heb de juiste data boven water getoverd met behulp van een select-query (in dit geval met een dubbele join), maar hoe maak ik daar nu een update-query van?

Voorbeeld

-- Select query

select node.title -- Dit is dezelfde tabel als welke wordt bijgewerkt
from node
inner join uc_products on node.nid = uc_products.nid
inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku;

-- Update query

update node
inner join uc_products on node.nid = uc_products.nid
inner join uc_product_beterenamen on uc_products.model = uc_product_beterenamen.sku
set node.title = uc_product_beterenamen.title;

Meerdere velden & meerdere records tegelijkertijd

Meerdere records bijwerken ahv. een identiek criterium

Da's nou precies waar SQL voor bedoeld is: Bewerkingen uitvoeren op datasets. Bv.:

update tbl_main set merk ="AFP" where productnaam like "%AFP%" and merk is null;

Meerdere records bijwerken ahv. verschillende criteria

Regelmatig kom ik situaties tegen met een lange reeks update-statements. Bv.:

update tbl_main set merk ="AFP" where productnaam like "%AFP%" and merk is null;
update tbl_main set merk ="Aer" where productnaam like "%Aer%" and merk is null;
update tbl_main set merk ="Ace" where productnaam like "%Ace%" and merk is null;
update tbl_main set merk ="Agi" where productnaam like "%Agi%" and merk is null;
update tbl_main set merk ="All" where productnaam like "%All%" and merk is null;
update tbl_main set merk ="Ani" where productnaam like "%Ani%" and merk is null;
update tbl_main set merk ="Bar" where productnaam like "%Bar%" and merk is null;
update tbl_main set merk ="Bes" where productnaam like "%Bes%" and merk is null;
update tbl_main set merk ="Bet" where productnaam like "%Bet%" and merk is null;
update tbl_main set merk ="Bit" where productnaam like "%Bit%" and merk is null;
update tbl_main set merk ="Bob" where productnaam like "%Bob%" and merk is null;
update tbl_main set merk ="Bon" where productnaam like "%Bon%" and merk is null;
update tbl_main set merk ="Can" where productnaam like "%Can%" and merk is null;
update tbl_main set merk ="Chu" where productnaam like "%Chu%" and merk is null;
update tbl_main set merk ="Dem" where productnaam like "%Dem%" and merk is null;
update tbl_main set merk ="Den" where productnaam like "%Den%" and merk is null;
update tbl_main set merk ="Dur" where productnaam like "%Dur%" and merk is null;
update tbl_main set merk ="Hun" where productnaam like "%Hun%" and merk is null;
update tbl_main set merk ="Dog" where productnaam like "%Dog%" and merk is null;
update tbl_main set merk ="Equ" where productnaam like "%Equ%" and merk is null;

Dan voel ik me altijd een beetje dom, maar veel efficiënter kan het volgens mij niet, omdat er voor elke set een ander criterium geldt.

Meerdere velden in één keer bijwerken ahv. identiek criterium

en een enkele keer kom ik een situatie tegen waarin ik per record graag alle kolommen wil bijwerken. Bv. om NULL-waardes om te fietsen naar lege velden voorafgaand aan een export naar een bestand. En weer is dat bij uitstek waar SQL goed in is. Bv.:

update tbl_main 
set 
   merk="AFP", 
   land="NL",
   beschikbaar="Ja"
where productnaam like "%AFP%" and merk is null;

Soms wil dat extreme vormen aannemen, zoals besproken in artikel Lege velden vervangen in alle tabellen (MySQL).

Update bij row number

Records in een tabel hebben geen gedefineerd rijnummer. Dat maakt het tricky om een bepaalde rij te updaten, maar niet onmogelijk. Bv.:

/*test data*/
create table foo (id int auto_increment primary key, a int);
insert into foo (a) values (10), (11), (12);

/*update statement*/
update foo
set a = 5
where id = (
   select id from (
      select id, @rownum:=@rownum + 1 as rownumber 
      from foo, (select @rownum:=0) vars order by id
   ) sq where rownumber = 2
);

Zie ook

Bronnen

http://dba.stackexchange.com/questions/21152/how-to-update-one-table-based-on-another-tables-values-on-the-fly