UPDATE with two Tables

General Discussion
1

I have two tables (postgres database)-
table1 has three columns ( m_part_number , m_part_name , assigned )
table2 has two columns ( fk_m_part_number , p_part_number)

I want to UPDATE table1’s “assigned” column (which is boolean) to True or False if a p_part_number is paired with fk_m_part_number in table2.

True if it is and False if it is not-

table1 expected results
123 | A | true
456 | B | true
789 | C | false

table2 sample data:
123 | 999
456 | 999
789 | 888
123 | 777
456 | 777
789 | 777

Here is my code:

UPDATE table1

SET assigned = CASE WHEN p_part_number = 999 THEN true
ELSE false
END

FROM table2
WHERE table1.m_part_number = table2.fk_m_part_number

The results I get from this code:
table1 results
123 | A | false
456 | B | false
789 | C | false

Thank you- Robert-

2

I want to UPDATE table1 ’s “assigned” column (which is boolean) to True or False if a p_part_number is paired with fk_m_part_number in table2

Do you mean if a m_part_number in table1 is paired to fk_m_part_number in table2? There is no p_part_number in table1

Are you only concerned with part number 999 or would a view/join make sense?

3 
create table table1
	(m_part_number int
	, m_part_name char(1)
	, assigned bit
	)

insert into table1
values 
	(123, 'A', 'True')
	, (456, 'B', 'True')
	, (789, 'C', 'False')

create table table2
	(fk_m_part_number int
	, p_part_number int)
insert into table2	
values
	(123, 999)
	, (456, 999)
	, (789, 888)
	, (123, 777)
	, (456, 777)
	, (789, 777)

select
	table1.m_part_number
	, m_part_name
	, case when p_part_number = 999 then 1 else 0 end
from table1
join table2 on table1.m_part_number = table2.fk_m_part_number

result:

123	A	1
456	B	1
789	C	0
123	A	0
456	B	0
789	C	0

I think that result explains the issue with your case when. There are multiple instances of part numbers and some match your criteria and others don’t

4

This should do what you want

update table1
set assigned = 1
where m_part_number in (
	select
		table1.m_part_number
	from table1 join table2
		on table1.m_part_number = table2.fk_m_part_number
	where table2.p_part_number = 999
)
5

I didn’t conceder using a SELECT- This option would have been plausible without the additional m_part_number results- (Should not see the second set of 123, 456, 789)-

6

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.

That seems like a “Work-Around” to me-

7

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.

UPDATE table1
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
8

SELECT DISTINCT ON (fk_m_part_number) * FROM table2

Without an order by the rows returned isn't guaranteed though, right? Same issue as the OP unless I'm missing something

For a select solution:

select
	m_part_number
	, m_part_name
	, case when m_part_number in (select
		fk_m_part_number
		from table2
		where p_part_number = 999
	) then 1 else 0 end as assigned
from table1

I don't think that join is necessary in my previous reply

9

It's a flaw in the premise of the original tables as given, regardless of the select used. One would assume there is another column in real life that would let you order it or filter it.

10

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-

11

It’s a flaw in the premise of the original tables as given, regardless of the select used

My select works perfectly :wink:

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

1 Like
12

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-
:+1:
Robert-

1 Like
13

No worries! There’s a reason Stack asks for “minimal” examples and there’s a reason you see “edited by” on a ton of questions. Just part of the game :slight_smile:

1 Like