I have two tables
Table1
Date Number Name Memo PaidAmount
2012-02-13 122361 John Doe some memo 245.25
2014-06-29 146352 Steve Doe another memo 360.34
Table2
Date FileNumber Name Address Price Status
2012-02-13 122361PH John Doe some memo 245.25 Pending
2014-06-29 146352AP Steve Doe another memo 360.34 Pending
I need to update records' Status in "Table2" from Pending to Closed only in records which FileNumbers' first six digits match first six digits of "Number" in "Table1".
And here is what's in my head...
UPDATE Table2
SET Status = Closed
Where Table2.FileNumber (first six digits) = Table1.Number (first six digits)
UPDATE Table2
INNER JOIN Table1 ON substr( Table2.number, 1, 6 ) = substr( Table1.number, 1, 6 )
SET STATUS = 'Closed'