This option handles the p_part_number that = 999, but doesn’t return the other m_part_number to false- So far, the only thing I’ve gotten to work is to have two UPDATES. One that sets all the m_part_numbers to false; then do another UPDATE that matches your update code.
For some reason, your original update query worked for me, but just in case, using DISTINCT ON should just give the first set of rows.
SET assigned = CASE WHEN p_part_number = 999 THEN true ELSE false END
FROM (SELECT DISTINCT ON (fk_m_part_number) * FROM table2) table2
WHERE table1.m_part_number = table2.fk_m_part_number
Jordan: You are correct- It does work when I run it the first time around- (When all the assigned rows are pre-set to false)- But, when its run a second time with a different part number; my code fails to set the rows that are not paired back to false- (That is the reason for my “Work-Around” works)-
It makes me think that a restructuring of the tables would be a better solution-
It’s a flaw in the premise of the original tables as given, regardless of the select used
My select works perfectly
One would assume there is another column in real life that would let you order it or filter it
I assumed the data was just wonky and that’s what we had to work with
It makes me think that a restructuring of the tables would be a better solution
If restructuring is an option then I’d be inclined to agree. I don’t know what data you’re working with but the example seems a little awkward. Maybe read up on normalization and see what you can apply to your use case
I attempted to simplify the data and tables for this thread- I missed the mark because it came out less than clear- My apologies- I currently have two updates in the script as a temporary solution and I’ll restructure the tables as the final solution. I really appreciate the time both of you contributed to helping-