Copying row in sql database and changing one value

[jcmeyer]

I would like to copy a row in a database and change one value.

I think that this is close to what I am supposed to do.

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT (?, C2, C3, C4, C5) FROM DatabaseName WHERE C1=?", [newC1Value, copiedC1Value])

Although, I get an operational error:
sqlite3.OperationalError: near ",": syntax error

Thank you for any suggestions!

[jice]

I'd write

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT (C1, C2, C3, C4, C5) FROM DatabaseName WHERE C1='%s'", [newC1Value])

[jice]

Sorry, my first post is not good. yours was the good one.
You can do this too :

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT (%s, C2, C3, C4, C5) FROM DatabaseName WHERE C1='%s'" % (newC1Value, copiedC1Value))

[jcmeyer]

That is still not working for me. I think I might know why.

What is the line after this sql execute line? Is it simply

self.connection.commit()

With the SELECT in my sql line do I need to fetch anything first before I commit?

[jcmeyer]

I figured it out.

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT ?, C2, C3, C4, C5 FROM DatabaseName WHERE C1=?", [newC1Value, copiedC1Value])
self.connection.commit()

The problem was the parentheses. I got rid of them in the SELECT and it works. Thanks for you help!

[sneekula]

jcmeyer, could you do me a favor and test this in your code, it makes things a lot more readable, and may avoid errors like you exerienced:

cur_ex = """
INSERT INTO DatabaseName (C1, C2, C3, C4, C5) 
SELECT ?, C2, C3, C4, C5 FROM DatabaseName WHERE C1=?
"""
self.cursor.execute(cur_ex, [newC1Value, copiedC1Value])
self.connection.commit()

BTW, nice solution on your part.

[jcmeyer]

Yes, sneekula's solution works as well. Thanks.

[gerard4143]

Its been some time since I did any SQL but won't it be a more natural fit to use UPDATE WHERE to update a record

[jcmeyer]

I was not trying to update a record. I wanted to copy a row, but change one value in it. In other words, I wanted to insert a new row into my database with all the exact same values of another row, but one, to prevent identical rows.

With UPDATE there are no new rows inserted into the database.

[webstart]

Hello

         (u'D:\\data\\Example.xml',)

     I read the above string from a list and pass it to the below query, 
             sql = "DELETE FROM docs WHERE pathVariId = '"+myfile+"'"
             self.cursor.execute(sql)

    but I get the error as below
        sqlite3.OperationalError: near "D": syntax error

any suggestions to get rid of that '\' which is causing the error, thanks

[TrustyTony]

You could try triple quote the outer double quotes for query. Path name seems Ok, it is only single back slash escaped.
Next time do not hijack old thread but make your own, include link to old thread if necessary.

[webstart]

Thank you, I did try with triple quote and '?' placeholder but the issue was with passing the string as unicode(myfile) instead of str(myfile)

Sorry for posting to the old thread as I came here while searching for the same error message. Thanks a lot for being patient, humble, putting your time and answering silly questions. which is far better than stackexchange ;)