SQL update based on column in other table

Question

Using SQLite, I am trying to update three columns based on another table (two columns)

The three columns are (Table1): 'AgentCreatedID' 'AgentOwnedID' 'AgentSentID'

The other table (Table2) consists of 'AgentID' and 'Designation'.

If the ID in one of the three columns matches the 'AgentID' in the second table, I want the 'Designation' value to populate. This table is a list of ALL unique IDs and the corresponding designation. Each row of data has a Creator, Owner, and Sender. I need to see what designation that person is from.

In Access, this would look something like this for the first value. I would also need to add the other two values. UPDATE Table1 LEFT JOIN Table2 ON Table1.AgentCreatedID = Table2.AgentID SET raw.AgentCreatedID = [ Table2 ]![ Designation];

I am not sure what that ! command is or how it could be used in SQLite.

score 0 · Answer 1 · answered Aug 29 '14 at 12:00

0

SQLite does not suport joins in an UPDATE statement.

You have to look up the new value with correlated subqueries:

UPDATE Table1
SET AgentCreatedID = (SELECT Designation
                      FROM Table2
                      WHERE AgentID = AgentCreatedID),
    AgentOwnedID   = (SELECT Designation
                      FROM Table2
                      WHERE AgentID = AgentOwnedID),
    AgentSentID    = (SELECT Designation
                      FROM Table2
                      WHERE AgentID = AgentSentID)

answered Aug 29 '14 at 12:00

CL.

173,858
17
217
259

Is there a way to do this in new columns instead of completely overwriting the AgentID columns though? Like perhaps I could add an ALTER TABLE command first to add three new columns on my Table1? Then refer to Table2 like you did above? – trench Sep 01 '14 at 15:50
Yes, you could do three [ALTER TABLE](http://www.sqlite.org/lang_altertable.html) statements. – CL. Sep 01 '14 at 16:31

score -2 · Answer 2 · answered Aug 28 '14 at 21:18

-2

The exclamation mark is used to separate the worksheet name from the reference in that worksheet. Here is Microsoft's explanation of cell references.

Now that you know what [ Table2 ]![Designatio] means, you can simplify it to use only the column name.

answered Aug 28 '14 at 21:18

DDay

698
1
9
17

You are correct. I was thinking Excel, not Access. Access uses exclamation mark for fields [http://stackoverflow.com/questions/2859536/exclamation-marks-in-a-query-sql]. – DDay Sep 03 '14 at 19:21

SQL update based on column in other table

2 Answers2