Happy New Year. A table and a database walked into a bar …
Actually, I have a table called TEMP and a table called SAVED with identical columns including an ID column with unique values. For each row in TEMP, I want to look in the SAVED table to see if it has a row with a matching value in the ID column. If it does, I want to update a column named STATUS in the TEMP table with the contents of the STATUS column from the STORED table.
In case it matters, the SAVED table is a simple DB query from a database so I could eliminate the SAVED table and just do a lookup on the database if that would be easier.
I’m tempted to use an inner join, but I’d be happy to hear about a more elegant solution.
You established the relation between the table TEMP and SAVED, which is the column ID on both. But what’s the relation of the table STORED? Which table or tables can it be joined to, and on which column or columns?
I believe the inner join is the elegant solution. The non-elegant solution would be to loop through the TEMP table and check if a matching line exists in SAVED row-by-row. Don’t try this at home.
As bruno mentioned, you didn’t specify how STORED can be joined. The most elegant solution could look like this: Join all three of those tables together, then update TEMP with the result. Maybe even without the data leaving the DB.
Thanks. And apologies to all for the confusion on STORED. I really try to boil my questions down to their essentials and write them to be unambiguous. When I first wrote the question, I used the name STORED for my saved table, then thought SAVED might be a slightly better name, but I failed to change every instance. Cheers.