# Mysql to Sqlite Update SET not working

**URL:** <https://www.ruby-forum.com/t/mysql-to-sqlite-update-set-not-working/171910>\
**Category:** Ruby\
**Created:** [July 11, 2009, 10:30am UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-update-set-not-working/171910 "2009-07-11T10:30:23Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [July 11, 2009, 10:30am UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-update-set-not-working/171910/1 "2009-07-11T10:30:23Z")

</div>

Hi,

I have been trying to get UPDATE SET working to move data from MYSQL to  
SQLite3.

The code is as follows:

#!/usr/bin/env ruby

require ‘dbi’  
require ‘rubygems’  
require ‘sqlite3’  
require ‘mysql’

my1 = Mysql.real\_connect(“server”, “username”, “password”, “db”)  
my2 = SQLite3::Database.open( ‘myapp/db/development.sqlite3’ )  
my2.results\_as\_hash = true

#stmt = my2.prepare(“insert into usages ( device\_id, bytes\_sourced )  
values( ?, ? )” )

stmt = my2.prepare(“UPDATE usages SET bytes\_sourced=‘row[1]’ where  
device\_id=‘row[0]’”)

result = my1.query(“SELECT device\_id, bytes\_sourced FROM odma\_unit”)

result.each do |row|  
stmt.execute(row[0], row[1])

my1.close  
end

When I run the code:

print print key + ’ = ’ + value  
print “\n”

I get a print out to screen of all the items.  
If I run:

stmt = my2.prepare(“insert into usages ( device\_id, bytes\_sourced )  
values( ?, ? )” )

I get entries in the database, obviously if i run it again i get  
duplicate entries.

I just want the code to compare the field device\_id in the MYSQL to  
device\_id in the SQLite database and thenif there is one to simply  
update the bytes\_sourced field. I have tried all sorts of combos for the  
stmt and the script runs without errors but does not insert. Its a hash  
to database entry and I even had the following code:

result.each do |key, value|  
stmt.execute(key, value)

I tihnk I have an error in my result statement.Does anyone have any  
advice?

Thanks

Gigg

---

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [July 12, 2009, 5:24am UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-update-set-not-working/171910/2 "2009-07-12T05:24:46Z")

</div>

> I tihnk I have an error in my result statement.Does anyone have any  
> advice?

I’ve heard some people suggest somehow using active record to go between  
two db’s. Never done it myself tho.  
=r

---

<div class="post-metadata">

**Author:** ![gigg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/13edae/32.png) [@gigg](https://www.ruby-forum.com/u/gigg)\
**Post date:** [July 13, 2009, 7:37am UTC](https://www.ruby-forum.com/t/mysql-to-sqlite-update-set-not-working/171910/3 "2009-07-13T07:37:40Z")

</div>

2009/7/11 Grant G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> […]  
> I have been trying to get UPDATE SET working to move data from MYSQL to  
> SQLite3.  
> […]

Hello,

I would suggest checking the SQLite3 documentation for the ‘INSERT OR  
REPLACE …’ syntax, which may be exactly what you need.

cu,  
Thomas