Update table using multiple table qualifications
Posted on 2016-09-12
I need to update a temp table with the country and email. There are 3 tables I need to use in my update...
The linking is the member_id
The thing is, and as you could probably imagine, there can, and usually is more than 1 record for order.
What I need is to get the address.country and address.email1 value into the temp table.
Of course, this won't work because there will be a single-row subquery returns more than one row, but here is an example...
update temp t
set (t.country,t.email1) = (select a.country,a.email1
from address a
where t.member_id = a.member_id
and a.member_id = (select o.member_id)
from order o
where o.status in ('M','C')))
I also tried doing an inner-join, which is probably completely wrong, but here's what I tried...
a.country as countrya,
t.country as countryt,
a.email1 as email1a,
t.email1 as email1t
from address a inner join temp t on a.member_id = t.member_id
inner join order o on a.member_id = o.member_id
where o.status in ('M','C')
set b.countryt = b.countrya,
b.email1t = b.email1a
This is returning a "cannot modify a column which maps to a non key-preserved table".
And, as you can see, I'm still not sure how I'd get the MAX order.order_id so I'd only get the member_id for the last order.
Maybe this would just be easier to create an Stored Proc.