Optimize / Improve my SQL update

Question

Okay, so let me just state this first. I am very new to SQL and I don't know a lot about it. I just recently started using it and I've been trying to learn it and improve. Please explain anything you state, as I may not understand common terms etc.

The SQL code I wrote now works like I intend it to, but the string seems obnoxious and made me think that this can't be quite right.

INSERT INTO reputations(uuid, warn, bandate, reasons, mods)
    VALUES ('{0}', '{1}', '{2}', CONCAT(reasons, '{3}'), CONCAT(mods, '{4}'))
    ON DUPLICATE KEY UPDATE warn = '{1}',
                            bandate = '{2}',
                            reasons=CONCAT(reasons, '{3}'),
                            mods=CONCAT(mods, '{4}')

In my Java code it looks like this: SQL in Java

As you also probably can see, I have no clue on how to properly format/indent it all for use like this. Any tips would be greatly appreciated.

I should also explain that the {n}'s are just spaces where I replace it with the information I want. For example, I replace all {0} with an UUID.

So, my question is. Is there a better way to do this? Any tips or help would be appreciated. Cheers!

You just reinvented prepared statements, but without the robustness and the efficiency they provide. http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — JB Nizet, Nov 09 '14 at 13:54
if you are using Eclipse, see [this](http://stackoverflow.com/questions/1820908/how-to-turn-off-the-eclipse-code-formatter-for-certain-sections-of-java-code) for how to disable the format so you can format the SQL as you want. — Arturo Volpe, Nov 09 '14 at 14:09

score 1 · Answer 1 · answered Nov 09 '14 at 14:53

I don't understand this part:

VALUES ('{0}', '{1}', '{2}', CONCAT(reasons, '{3}'), CONCAT(mods, '{4}'))
------------------------------------^

The use of the column name there should be generating an error.

Perhaps this is the statement you want:

INSERT INTO reputations(uuid, warn, bandate, reasons, mods)
    VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')
    ON DUPLICATE KEY UPDATE warn = VALUES(warn),
                            bandate = VALUES(bandate),
                            reasons = CONCAT(reasons, VALUES(reasons)),
                            mods = CONCAT(mods, VALUES(mods));

In the ON DUPLICATE KEY part, you can use VALUES() to get the new value from a particular column. In addition, I suspect you might really want to have a separator for the last two concatenations:

                            reasons = CONCAT_WS('; ', reasons, VALUES(reasons)),
                            mods = CONCAT_WS('; ', mods, VALUES(mods));

The use of CONCAT_WS() has a nice side effect if the values are ever NULL -- it will still leave the remaining values.

Optimize / Improve my SQL update

1 Answers1