sqlite - Unsupported Sql Update With From

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Attach
• Contents
• Diff
• Edit
• History
• Text

Postgres also allows "UPDATE ... FROM ... ", BTW. (As does Ingres --CAU)

I was working on something where I really wanted to use this construct with SQLite, so I came up with the following hack:

		--
		-- SQLite does not allow "UPDATE ... FROM"
		-- but this is what it might look like
		--
		UPDATE
			t1
		SET
			measure = t2.measure
		FROM
			t2, t1
		WHERE
			t2.key = t1.key
		;

		--
		-- emulating "UPDATE ... FROM" in SQLite
		--
		-- n.b.:  it assumes a PRIMARY KEY !
		--
		-- the INSERT never succeeds because
		-- the JOIN restricts the SELECT to
		-- existing rows, forcing the REPLACE
		--
		INSERT OR REPLACE INTO
			t1( key, measure )
		SELECT
			t2.key, t2.measure
		FROM
			t2, t1
		WHERE
			t2.key = t1.key
		;

Since that works, maybe SQLite could be made to support the "UPDATE ... FROM" construct directly, so we would not have to rely on conflict resolution to do essentially the same thing (not exactly the same, since REPLACE is DELETE and INSERT, but sometimes close enough). < gifford hesketh::2004- Oct-26

I've managed successfully to do this an alternative way, works in version 3.2.1 (--CAU:18-Aug -2005) ...

		--
		-- emulating "UPDATE ... FROM" in SQLite
		--
		--
		UPDATE
			t1
		SET
			measure = ( SELECT measure FROM t2 WHERE t2.key =
t1.key )
		;

But be very careful with this code! The original joined update would only update where a record is found with the key in both tables. This, on the other hand, will update everything in t1, setting measure to NULL if there isn't a record in t2. TJH:2006-03-24

A variation on this theme so your measure will not be set to NULL if there is no data. robewald:2007-02-19:

		--
		-- emulating "UPDATE ... FROM" in SQLite
		--
		--
		UPDATE
			t1
		SET
			measure = ( SELECT measure FROM t2 WHERE t2.key =
t1.key ) WHERE t1.key = ( SELECT key FROM t2 WHERE t2.key=t1.key)
		;



*Kamil:2007-02-22*


or something like that that is more clearer to understand for me:


UPDATE t1


SET measure = ( SELECT measure FROM t2 WHERE t2.key =t1.key )


WHERE EXISTS (SELECT * FROM t2 WHERE t2.key=t1.key);