How to UPDATE in SQLite using a LEFT JOIN to select candidate rows

Question

I have a table assoc containing columns

local_id, remote_id, cachedData

I can successfully run an SQLITE query that looks like

SELECT a1.local_id, a1.remote_id FROM assoc a1 LEFT JOIN ....

so that I identify certain rows of the assoc table that meet my criteria.

What I would like to do is to set cachedData to null in those rows.

How can I do this? Sqlite doesn't support UPDATE with joins; you can issue subqueries but I can't figure out how to get the syntax correct; it seems nonintuitive to me.

Crap, I was afraid you'd say that. :) I've [asked this question](http://stackoverflow.com/questions/4622453/where-col1-col2-in-sql-subquery-using-composite-primary-key) in anticipation of this being the case. If I get the goods, I'll update my answer accordingly. — Phrogz, Jan 07 '11 at 04:17
If I can figure out how to ALTER TABLE to add an autoincrementing id, I could use that one instead; I can add an additional column. — Jason S, Jan 07 '11 at 04:19
(e.g. the primary key has to remain (local_id, remote_id) but I can add another key for disambiguation) — Jason S, Jan 07 '11 at 04:20

score 3 · Accepted Answer · answered Jan 07 '11 at 04:37

3

 UPDATE assoc SET cachedData = NULL
    WHERE EXISTS (SELECT * FROM otherTable 
        WHERE otherTable.Col1 = assoc.Col1 AND otherTable.Col2 = assoc.Col1)

Be aware that this is not especially performant.

answered Jan 07 '11 at 04:37

Larry Lustig

49,320
14
110
160

your technique worked for me in the short term, I think rowid might be the best thing to use longer term. – Jason S Jan 07 '11 at 04:46
Be careful, ROWID can be reused. You must rigorously enforce the relation. – Larry Lustig Jan 07 '11 at 04:47
@Larry: "rigorously enforce the relation" -- could you elaborate? If this is used only for a single update statement, is there a chance that it is unsafe to use? – Jason S Jan 07 '11 at 13:33
If rows were to be deleted in the "primary" side of the relation, but the corresponding rows were not deleted on the "foreign" side, then new rows added back to the primary-side you would have orphaned foreign-side rows pointing to the new primary-side records. But you have an earlier issue, which is how to get the ROWID of the primary-side records into the foreign-side table without the JOINed update syntax. – Larry Lustig Jan 07 '11 at 13:44
So, if the two tables are not going to have new rows added or removed, your technique is safe but simply moves the UPDATE issue one step earlier, to when you need to establish the ROWID link between the tables. – Larry Lustig Jan 07 '11 at 13:45
But it's the same table `assoc` that has the rowid, it's not two tables, so rowid isn't exported to or imported from anywhere else. I'm just joining the `assoc` table to another table to select which rows in the `assoc` table are being updated, and using rowid as a primary key to tell update which rows are updated. – Jason S Jan 08 '11 at 03:25

score 0 · Answer 2 · answered Jan 07 '11 at 03:53

0

If assoc has a single column as the primary key (and assuming that it is local_id):

UPDATE assoc
SET cachedData=NULL
WHERE local_id IN (
  SELECT local_id FROM assoc a1 LEFT JOIN ...
);

answered Jan 07 '11 at 03:53

Phrogz

296,393
112
651
745

What if it has more than one column as the primary key? – Jason S Jan 07 '11 at 04:16
@JasonS I'm afraid I have no answer as of yet for the composite PK situation. Adding a unique key for disambiguation as you suggest above would be one solution. – Phrogz Jan 07 '11 at 04:27

score 0 · Answer 3 · answered Jan 07 '11 at 04:45

0

Aha, there's already a builtin rowid!

UPDATE assoc
SET cachedData=NULL
WHERE rowid IN (
   SELECT rowid FROM assoc a1 LEFT JOIN ...
);

answered Jan 07 '11 at 04:45

Jason S

184,598
164
608
970

How to UPDATE in SQLite using a LEFT JOIN to select candidate rows

3 Answers3

Linked