CODEWITHSUNDEEP
mysql
Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385164

Is there a trick to allow MySQL to "ignore" a trailing comma in the SET clause of an UPDATE statement?

From C++, I'm generating an UPDATE statement programmatically in a way that makes stripping a trailing comma difficult:

UPDATE `myTable` SET 
   `Field1` = "value",
   `Field2` = "value",
   `Field3` = "value",
WHERE `Field4` = "value";

Is there some static, no-op key/value pair I can insert after the final column value specification, which would make the trailing comma "okay"? Or will I have to complicate my C++ code to avoid writing it entirely?

Something apparently equivalent to the following invalid approach would be nice.

UPDATE `myTable` SET 
   `Field1` = "value",
   `Field2` = "value",
   `Field3` = "value",
--- 1 = 1
WHERE `Field4` = "value";

Upvotes: 2

Views: 3266

Answers (5)

LPodolski
LPodolski

Reputation: 2878

You can place comma before data, as follows:

UPDATE `myTable` SET 
   `Field1` = "value"
 , `Field2` = "value"
 , `Field3` = "value"
WHERE `Field4` = "value";

Upvotes: 0

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385164

One way to achieve this, given that you will have to do so in your C++ code, and assuming that you are using a std::ostream to build up the statement, is to pull back the "put" cursor one byte so that the next character replaces the trailing , rather than following it:

#include <iostream>
#include <sstream>

int main()
{
    std::stringstream ss;
    ss << "UPDATE `myTable` SET ";

    ss << "`A` = \"value\","
       << "`B` = \"value\",";

    ss.seekp(-1, std::ios_base::cur);
    ss << " WHERE `C` = \"value\"";


    std::cout << ss.str() << '\n';
}

// Output: UPDATE `myTable` SET `A` = "value",`B` = "value" WHERE `C` = "value"

This may be more feasible and/or efficient than playing with string lengths, depending on what exactly your code looks like.

Upvotes: 1

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385164

Unless you're willing to duplicate a value as Igoel suggests (which may not be ideal if the value is lengthy), the simple answer is no.

One briefly encouraging possibility was the use of the alias NEW to represent the incoming values, so that the final value may be duplicated without actually having to present it in the query again (and I'd hope that this would be taken out by the query optimiser):

UPDATE `myTable` SET 
   `Field1` = "value",
   `Field2` = "value",
   `Field3` = "value",
--- `Field3` = NEW.`Field3`
WHERE `Field4` = "value";

Alas, this is not supported in an UPDATE statement, only inside a trigger body.

You'll have to do the manipulation in your C++ before executing the statement, either through character replacement (, for ) or character removal; the former may produce more complex code than you have now, and the latter may be inefficient (depending on the structure of your query-building code), but it's still your best bet.

Upvotes: 2

LeGEC
LeGEC

Reputation: 51850

I don't see any elegant way to do it inside the mysql query.

In your C++ code, however, there are a number of ways to do this :

  • removing the last coma after creating the SET clause with an extra coma should be as simple as : qry[lgth-1] = ' '; or qry.erase(qry.length()-1);

  • I imagine you have some sort of loop to build the SET clause, checking the index i in your loop is the classical way, or build an array of strings and use some join(stringList, separator) function

Upvotes: 0

Jacob A.
Jacob A.

Reputation: 270

you could dublicate one of the rows and strip the comma like:

UPDATE `myTable` SET 
   `Field1` = "value",
   `Field2` = "value",
   `Field3` = "value",
   `Field3` = "value"
...

Upvotes: 0

Related Questions