Create MySQL UPDATE statement as output

Question
1

Hi there,

I just browsed through the forum for a while and I am really excited, what easydatatransform can do. I use it for some time now, mainly for creating MySQL INSERT statements and clean Excel data.

But now now I have a challenge I cannot solve myself: I have a table of data which I want to get into a MySQL table. Because I the records exist already in the database, it will be an UPDATE statement. While in INSERT you can just leave empty cells in the statement, I can’t do it in UPDATE, because it will overwrite the existing value. The UPDATE statement follows the syntax:

UPDATE table SET 
	column_name2 = value2,
	column_name3 = value3,
WHERE column_name1 = value1;

INSERT statements I created in easysdatatransform with the Substitute transformation (Substitution script):

INSERT INTO lime_survey_331974 (id, token, submitdate, 331974X61X14531, 331974X62X592VAbuss) VALUES ($(id), $(token), $(submitdate), $(331974X61X14531), $(331974X62X592VAbuss));

But If I see it right I cannot use this script for UPDATE because I see no way to delete empty values later or in the transformation.

I also tried to use the Concat plus Replace transformation I found in this forum post: Concat columns, skipping blanks. This way I only get the values in the statement and could even delete the empty values, but could not find a way to add the column names.

Anybody knows a solution where can delete the empty cells AND include the column names in the statement?
Doc Rolfo

2

Can you give me a few example rows of the input you have and the output you want?

3

Hi,
sure! I tried to attach a small version of the data table but could not upload it because I am a new user. So I just paste the csv here:

U_ID;Adresseigner;Aktiv;MEBaZaehlung;Unternehmen;Rooms_Max;KapazBiggest;KapazGesamt;QuellenID;Bemerkung
102113;;N;N;;;;;;Hat keine relevanten Kapazitäten mehr
102108;;N;N;;;;;;Doppler
121832;MEBa-Partner;;;;;;;;
102161;;;;Alice Rooftop;;250;250;;
101842;;;;;4;222;222;;

The output would look like this:

Line 2: UPDATE db_table SET Aktiv = “N”, MEBaZaehlung = “N”, Bemerkung = “Hat keine relevanten Kapazitäten mehr” WHERE U_ID = 102113;

Line 5: UPDATE db_table SET Unternehmen = “Alice Rooftop”, KapazBiggest = 250, KapazGesamt = 250 WHERE U_ID = 102161;

So it should just be skipping all the blank cells and only using the cells which contain a value. Is this somehow possible?
DocRolfo

4

You can do this with Replace and a regular expression.

,?\s?\S+\s?=\s?“”

Here is my quick and dirty attempt. Someone might have a more elegant approach.

DocRolfo.transform (3.1 KB)

Ps/ The Substitute is only set up for some columns, not all.

5

@DocRolfo,

Here you go.

image
image789×547 69.1 KB

Transform file.
Create MySQL UPDATE .transform (4.3 KB)

1 Like
6

Interesting way to avoid regex.

BTW You might need extra steps with either approach if the data contains commas or quotes.

7

This way, one does not have to type the column names which was one of the requirement.

1 Like
8

Wow - thanks a lot!

Yeah Regex is a sore spot of mine… but I also did not get the idea to use it in this case!

Anonymous, your way was very interesting - I never thought of going this way. I took your steps and added just one step: when the values are numbers/integer, they are not allowed to be inserted in “”. So I added one Replace and now the transformation is just how I need it to be!

I attached the file again - aaaaach shoot… I cannot upload files! Sorry, would have liked to show you.

Admin - as you said, your way was quick and I learned a lot about transforming text in easydatatransform. I also tried to work the restriction in here that integers need to be without “” but it did not work. I tried to add a third Replace field where I replace

,?\s?\S+\s?=\s?“\b[0-9]+\b”

with

,?\s?\S+\s?=\s?\b[0-9]+\b

but in Update statement it stays Rooms_Max = "4
I also wanted to attached the file - as I said above: no chance. Too bad.

Anyway, thanks for the help, guys… and I really have to get into Regex!
DocRolfo

9

There are some useful regex resources linked at:

10

VERY interesting! Thanks a lot!

11

Here is the updated transform that will not put numbers/integer in quotes.

image
image814×568 66 KB

Transform file.
Create MySQL UPDATE .transform (4.2 KB)

1 Like
12

Here you go with the solution provided by @Admin, by modifying it your requirement of numbers/integers not to be in quotes. Also I added all the columns.

image
image812×318 28.4 KB

Modified Transform file.
DocRolfo.transform (3.4 KB)

1 Like
13

Thanks a lot, guys, for your great help! Appriciate it a lot!
Doc Rolfo

1 Like