Update a column in one table based on a not null value in a joined table

Thanks drew.allen for your help. I need to update the joined table only once per month but it should be "triggered" when the table without the Boolean value is truncated and reloaded. I know how to do an updated on a joined table with a T-SQL script but I don't know how to do it upon loading. Should I use SSIS?

Ideally I'd like to set the Boolean value in the base table to 1 if there is a matching row in the joined table or a 0 if there is not a match. I could just run the update query every month but I wondered if there was a better way. I'm googling updatable views and triggers to see if I can find a case like mine where it's the base table that gets updated based on what's found in the joined table. I haven't used triggers before but I've seen them in the wild.

I'll post back if I ever solve this. Thanks.

You should have mentioned that this was part of a monthly process. In that case, you should simply update your process to update the second table based on the first table whenever this process is run.

My original statement was based on the assumption that the update could appear anywhere. Also, you don't need to use an updateable view. The reason I suggested a view was, because it runs based on the current data instead of storing the data in a field that needs to be updated.

Drew

Yes, sorry I didn't mention that but the truth is that i really would like the value in the base table to automatically update whenever the value in the joined table updates. I added the monthly to "loosen up" the requirements so that I might get more replies about what to do. I can add it to SSIS but I was hoping that there was something else I could do like triggers (but I've always heard they were "bad" so that's why I'm asking what other alternatives are there. Maybe I have a mental block and there aren't any other ways but I still think I probably do have a blockage.

Okay, if you just want to show the value in a webform, you don't need to have a field in the table. You'll want to do this in the select statement you execute to retrieve data for your webform:

SELECT L1.lastname,

L1.firstname,

L1.DOB,

L1.col3,

R1.cola,

CAST(CASE WHEN R1.lastname IS NULL THEN 0 ELSE 1 END AS BIT) AS ACGBoolean

FROM L1

LEFT JOIN R1

ON R1.lastname=L1.lastname

AND R1.firstname=L1.firstname

AND R1.DOB = L1.DOB

or something like that. Of course I don't know which columns you use to join the tables. I have just put in the ones that are in both your example tables.

I would agree that you don't need to update a flag column in your left table. You are working with relational tables here, so the left join itself tells you of the existence of the row in the outer table. It does not make sense to store a flag in one table to tell you if values exist in a related table. That is what the JOIN does for you. Considering that you have to create the JOIN to do the update statement, and you want it as near real time as possible, you are trying to circumvent the benefits of a relational database!

Kaj's query suggestion is what I'd go with. Use the OUTER JOIN to tell you if the row exists in the outer table or not. You could call this query from your client side, or put the query into a view and call the view. Either way, storing the flag and trying to keep it up to date (possibly with triggers) is only going to cause you problems in the long run.

Thanks Kaj. This works for me.

Thanks John Rowan, I'll use Kaj's answer. I appreciate the confirmation.