Database design: when in doubt - use meta tables

It’s becoming quiet intriguing when you work with the 3rd party closed-source projects. You can see how the project was maturing, and how things were changing on the fly. Especially when you have to plug in some extra logic on top of it, and the code is obfuscated, so the only way how to find all ins and outs - check the database schema. Certain systems force to develop evil workarounds to coupe with bad design practices, whether it’s code or database wise....

September 22, 2016 · 2 min · anvyst

SQL reinsert row in the same table

Seems that we have a competitor to “export to csv” SQL query that I keep forgetting every time I need to export something in the CSV file. The second nominee is reinserting the row into the same database but with a new primary key. # YOURID - source record # yourtable - target table; CREATE TEMPORARY TABLE tmp_table SELECT * FROM yourtable WHERE id=YOURID; SELECT @new_id:=((SELECT id FROM yourtable ORDER BY id DESC LIMIT 1) + 1); UPDATE tmp_table SET id=@new_id WHERE id = YOURID; INSERT INTO yourtable SELECT * FROM tmp_table WHERE id=@new_id;

February 10, 2014 · 1 min · anvyst