By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,826 Members | 1,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,826 IT Pros & Developers. It's quick & easy.

sqlite3 - SQL question regarding update of a table based on lookup table

P: 1
I have a table AVI with the following relevant structure:
POLY_NUM key
LKP
SITE1
SITE2
SITE3
Y2BH

I have a lookup table SILKP with:
LKP
SITE1
SITE2
SITE3
Y2BH

what is the fastest/most efficient way to update table A SITE1-SITE3 and Y2BH fields by linking via lookup.

What I have:

Expand|Select|Wrap|Line Numbers
  1. with sqlite3.connect(db_filename) as conn:
  2.     cursor = conn.cursor()
  3.  
  4.     print "Updating lookup fields..."
  5.  
  6.     cursor.execute("""
  7.                         UPDATE avi SET site1 = (SELECT site1 FROM silkp WHERE silkp.lkp = avi.lkp)
  8.                    """)
  9.     cursor.execute("""
  10.                         UPDATE avi SET site2 = (SELECT site2 FROM silkp WHERE silkp.lkp = avi.lkp)
  11.                    """)
  12.     cursor.execute("""
  13.                         UPDATE avi SET site3 = (SELECT site3 FROM silkp WHERE silkp.lkp = avi.lkp)
  14.                    """)
  15.                         UPDATE avi SET y2bh = (SELECT y2bh FROM silkp WHERE silkp.lkp = avi.lkp)
  16.                    """)
Is there a faster and more elegant way?
Feb 6 '13 #1
Share this Question
Share on Google+
1 Reply

Rabbit
Expert Mod 10K+
P: 12,441
You can try joining the tables and updating it in one statement. This works in SQL Server, you can see if it works in SQLite.
Expand|Select|Wrap|Line Numbers
  1. UPDATE t1
  2. SET field1 = t2.field1,
  3.     field2 = t2.field2
  4. FROM t1 INNER JOIN 
  5.      t2 ON t1.id = t2.id
Feb 6 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.