Updating table 1 based on table 2

[Einstein47]

Hey all,

I have an update like the following:

UPDATE table1 SET itemID = 1
 WHERE itemName = ( SELECT itemName FROM table2
                     WHERE itemID = 1 )

This works, but I have to modify the number each time and re-execute the SQL. Is there a way to have the SQL do the update for all values from table2?

Thanks in advance, Einstein47
("For every expert, there is an equal and opposite expert." - Arthur C. Clarke)

 

[aplusc]

Try this:

update table1 join table2 on table1.itemName = table2.itemName set table1.itemID = table2.itemID

 

[Einstein47]

I got an error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

If it is any help, I'm using an Access DB. Here is my SQL statement.

UPDATE tblPicks JOIN tblUsers ON
tblPicks.user = tblUsers.UserName
set tblPicks.userid = tblUsers.ID

Einstein47
("For every expert, there is an equal and opposite expert." - Arthur C. Clarke)

 

[PruSQLer]

I don't have time to test this out but wouldn't the following work


Update tblPicks Set tblPicks.userid = tblusers.ID
Where tblPicks.user = tblUsers.UserName

 

[PruSQLer]

Okay, so I tested that anyway and it doesn't work in Access. But, if you make it UPDATE tblPicks INNER JOIN tblusers, I think that might work.

 

[Einstein47]

Thanks - I'll give it a go - I'll let you know the results Einstein47
("For every expert, there is an equal and opposite expert." - Arthur C. Clarke)

 

[Einstein47]

I ended up using the following:

UPDATE tblPicks INNER JOIN tblUsers ON tblPicks.user = tblUsers.UserName
SET [tblPicks]![userid] = [tblUsers]![ID]

The square brackets may not be necessary, but for safety sake just incase you have a poorly named column (like me). Einstein47
("If computers ever get too powerful, we can organize them into a committee - that will do them in." - Unknown)