SQLite Update involving multiple tables

[xeir]

In case there are others out there that are use to MySQL and simple multi-table updates, this may or may not make things easier for you when trying to do the same with SQLite.

If anyone else has any ideas or perhaps an easier way to perform the same, I'd certainly be interested, but this was the solution I founf for the numerous updates I have to do.

Example MySQL query:

Code: Select all

UPDATE location l, import e SET l.companyName = e.companyName, l.contactName = e.contactName, l.phone = e.phone, l.hours = e.hours, l.addr1 = e.addr1, l.addr2 = e.addr2, l.city = e.city, l.county = e.county, l.state = e.state, l.zip = e.zip, l.noteLoc = e.noteLoc WHERE l.locID = e.locID AND e.subLoc = 1;

SQLite equivalent:

Code: Select all

set itemDelimiter to comma
put "location" into tTableDest -- MODIFY
put "import" into tTableSrc -- MODIFY
put "companyName,contactName,phone,hours,addr1,addr2,city,county,state,zip,noteLoc" into tSubFields -- MODIFY
   
put "UPDATE " & tTableDest & " SET " into tUpdate
put "= (SELECT " & tTableSrc & "." into tSubSelect
put "FROM " & tTableSrc into tSubFrom

put "WHERE " & tTableDest & ".locID = " & tTableSrc & ".locID AND " & tTableSrc & ".subLoc = 1), " into tSubWhere -- MODIFY

put "WHERE EXISTS (SELECT * FROM " & tTableSrc && tSubWhere into tWhere
delete char -2 to -1 of tWhere
   
put tUpdate into tQuery
repeat for each item qItem in tSubFields
  put qItem && tSubSelect & qItem && tSubFrom && tSubWhere after tQuery
end repeat
delete char -2 to -1 of tQuery
put space & tWhere after tQuery
   
--put tQuery into msg

This will create the following query:

Code: Select all

UPDATE location SET companyName = (SELECT import.companyName FROM import WHERE location.locID = import.locID AND import.subLoc = 1), contactName = (SELECT import.contactName FROM import WHERE location.locID = import.locID AND import.subLoc = 1), phone = (SELECT import.phone FROM import WHERE location.locID = import.locID AND import.subLoc = 1), hours = (SELECT import.hours FROM import WHERE location.locID = import.locID AND import.subLoc = 1), addr1 = (SELECT import.addr1 FROM import WHERE location.locID = import.locID AND import.subLoc = 1), addr2 = (SELECT import.addr2 FROM import WHERE location.locID = import.locID AND import.subLoc = 1), city = (SELECT import.city FROM import WHERE location.locID = import.locID AND import.subLoc = 1), county = (SELECT import.county FROM import WHERE location.locID = import.locID AND import.subLoc = 1), state = (SELECT import.state FROM import WHERE location.locID = import.locID AND import.subLoc = 1), zip = (SELECT import.zip FROM import WHERE location.locID = import.locID AND import.subLoc = 1), noteLoc = (SELECT import.noteLoc FROM import WHERE location.locID = import.locID AND import.subLoc = 1) WHERE EXISTS (SELECT * FROM import WHERE location.locID = import.locID AND import.subLoc = 1)

Modify the following to match your required update statement

tTableDest - Target Table
tTableSrc - Source Table
tSubFields - Fields to be updated (currently must be named the same in both tables)
tSubWhere - Where statement to specify which records to update

I'm sure this can be modified to handle more circumstances, target field name override, etc. fairly easily, maybe even altered to run as a function.

In the mean time, it works and just thought I would share.