0

What is the MySql UPDATE equivalent of this ?

INSERT INTO items (title, url) VALUES (%s, %s)", (listtitle[0], listlink[0]))
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
CharlieC
  • 123
  • 1
  • 8
  • 1
    Hmmm... why do you think than `UPDATE` can be equivalent of `INSERT` ever? It is really unclear what is your question.... – Andrey Korneyev Jan 29 '15 at 07:16
  • When one `INSERT` , it creates a new line, however with `UPDATE`, it edit's the existing line – CharlieC Jan 29 '15 at 07:18
  • But it is not equivalents - it is totally different commands intended for different usage. At least, with `update` you should provide some criteria for choosing records to be updated - not only new values. – Andrey Korneyev Jan 29 '15 at 07:20
  • Ah, but i would think that the message has been brought over such that people understand, I do however get your point or different usage. – CharlieC Jan 29 '15 at 07:22

2 Answers2

1
"update items
    set title = '{0}',
        url = '{1}'
  where COLUMN = VALUE
    and COLUMN2 = VALUE2".format(listtitle[0], listlink[0])

Replace the words in uppercase with the specific values or otherwise you would update the whole table.

Unknown column in 'field list' error on MySQL Update query

Try this

self.cursor.execute("UPDATE items SET descs ='{0}'".format(item['title'][0]))

instead of this

self.cursor.execute('UPDATE items SET descs =%s' % item['title'][0])

Community
  • 1
  • 1
Christoph Werner
  • 141
  • 1
  • 6
  • Yeah, did that too. Got an error which i cant seem to solve. Quoted from my reply to Gruby: Initially did that, it however results in a `error mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'Yii2' in 'field list'` Yii2 = a item in the list – CharlieC Jan 29 '15 at 07:24
  • Maybe you need to add backticks around the inserted values? – Christoph Werner Jan 29 '15 at 07:42
  • Tried that after u suggested that link, still gives the same error – CharlieC Jan 29 '15 at 07:46
  • Can you share the part of the code and the table structure with us? – Christoph Werner Jan 29 '15 at 07:51
  • Ok sure, it's actually a Pipeline code of a Web Crawler. Using Python and Scrapy Web crawler coupled with MySQL. http://hastebin.com/ajavifedav.py – CharlieC Jan 29 '15 at 07:57
  • Is the problem here: `self.cursor.execute("SELECT title,url FROM items WHERE title= %s", item['title'])`? – Christoph Werner Jan 29 '15 at 08:23
  • The problem actually lies with line 54. – CharlieC Jan 29 '15 at 08:30
  • I just tried `self.cursor.execute('UPDATE items SET descs = "%s"' % item['title'][0])` Quoted %s, it however only just inserts the last value of the list only, (http://puu.sh/fbiqb/bbb83251e1.png) – CharlieC Jan 29 '15 at 08:40
  • I meant single quotation marks instead of backticks some comments before, but I don't get what your problem is at the moment? You set in all rows the column `desc` to the value of `list['item'][0]` which seems to be 'Yii2'. Your `update` is working as expected or not? – Christoph Werner Jan 29 '15 at 08:50
  • No it's not, "Yii2' is the last value of the list, eg: list = [1,2,fwef,2,35y,h6h56,test, twenty,yii2]. I am trying to insert the value in order to the respective columns instead of the last value only – CharlieC Jan 29 '15 at 09:38
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/69804/discussion-between-christoph-werner-and-charliec). – Christoph Werner Jan 29 '15 at 10:55
  • Dont mind me asking, how would go one about illiterating through the rows ? – CharlieC Jan 29 '15 at 12:24
0

Try this:

"UPDATE items SET title = %s, url= %s" % listtitle[0], listlink[0]
Felipe Oriani
  • 37,948
  • 19
  • 131
  • 194
Gruby
  • 21
  • 2
  • Initially did that, it however results in a error `mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'Yii2' in 'field list' ` Yii2 = a item in the list – CharlieC Jan 29 '15 at 07:21