Update table using multiple table qualifications

I need to update a temp table with the country and email. There are 3 tables I need to use in my update...
temp
address
order

The linking is the member_id
temp.member_id
address.member_id
order.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...

update
(select a.member_id,
        t.member_id,
        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')
 ) b
 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.

Thanks,
Larry
biglarrrrAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
That error would tell me that the TEMP table doesn't have a field called MEMBER_ID.  I don't have your tables, so I cannot test anything.

However, if I create bogus tables with these statements:
create table temp (country varchar2(10), email1 varchar2(10), member_id number);
create table address (country varchar2(10), email1 varchar2(10), member_id number, address_id number);
create table orders (member_id number, status char(1));

Open in new window

The query runs just fine.  So, it is definitely an issue with the table on your end.

You can go with the MERGE statement if you want, but as this seems to be a temp table, I would have populated the TEMP table with an INSERT that got all the information you needed.  And, since you are only using one side of the MERGE you may not see any benefit.  It would also be a race condition as to which record you would get back as you stated there are multiples.
0
 
johnsoneSenior Oracle DBACommented:
I don't think there is enough information to get to where you need to be.  Based on your sample update statement, I don't see the need for the ORDERS table:
UPDATE temp t 
SET    ( t.country, t.email1 ) = (SELECT a.country, 
                                         a.email1 
                                  FROM   address a 
                                  WHERE  t.member_id = a.member_id); 

Open in new window

If that gives you single row sub-query returns too many rows, and you want to do it by max id then something like this (assuming ADDRESS_ID is the name of the field to use for MAX):
UPDATE temp t 
SET    ( t.country, t.email1 ) = (SELECT country, 
                                         email1 
                                  FROM   (SELECT a.country, 
                                                 a.email1, 
                                                 Row_number () 
                                                   over ( 
                                                     ORDER BY address_id DESC) rn
                                          FROM   address a 
                                          WHERE  t.member_id = a.member_id) 
                                  WHERE  rn = 1); 

Open in new window

As you are going after one member at a time, I don't believe you need a partition by there.
0
 
biglarrrrAuthor Commented:
Thanks for your reply johnsone. Sorry for not being more clear. The order table is required because I need to only grab those members who have placed an order. But, since a given customer can place more than 1 order, I can't use a.member_id = o.member_id because there could be more than 1 o.member_id returned.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
biglarrrrAuthor Commented:
BTW, I tried this:

update
(select a.member_id,
        a.country as countrya,
        t.country as countryt,
        a.email1 as email1a,
        t.email1 as email1t
from address a,temp t,
 (select ord.member_id, max(ord.orders_id) max_orders_id
  from orders ord
  where ord.status in ('M','C')
  group by ord.member_id) o
where a.member_id = t.member_id
and a.member_id = o.member_id) x
set x.countryt = x.countrya,
x.email1t = x.email1a

But I'm getting a:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Thanks,
Larry
0
 
johnsoneSenior Oracle DBACommented:
Are there actually rows in the TEMP table to update?  Sounds like you don't need an update but an insert.
INSERT INTO temp 
            (member_id, 
             countrya, 
             countryt, 
             email1a, 
             email1t) 
(SELECT a.member_id, 
        a.country, 
        o.country, 
        a.email1, 
        o.email1 
 FROM   (SELECT a.member_id, 
                a.country, 
                o.country, 
                a.email1, 
                o.email1, 
                Row_number() 
                  over ( 
                    PARTITION BY o.member_id 
                    ORDER BY o.order_id DESC) rn 
         FROM   address a 
                join orders o 
                  ON a.member_id = o.member_id 
         WHERE  o.status IN ( 'M', 'C' )) 
 WHERE  rn = 1); 

Open in new window

Sorry, but what you have posted so far is still confusing to me as to which fields you need and where they come from.

I'm also still confused as to why you need to join to the ORDERS table.  This makes more sense to me.
UPDATE temp t 
SET 
       ( 
              countrya, 
              email1a 
       ) 
       = 
       ( 
              SELECT country, 
                     email1 
              FROM   ( 
                              SELECT   a.country, 
                                       a.email1, 
                                       row_number() over (parition BY member_id ORDER BY address_id DESC) rn address a
                              WHERE    a.member_id = t.member_id 
                              AND      EXISTS 
                                       ( 
                                              SELECT 1 
                                              FROM   orders o 
                                              WHERE  o.member_id = a.member_id 
                                              AND    o.status IN ('M', 
                                                                  'C')) 
                              WHERE    rn = 1);

Open in new window

0
 
biglarrrrAuthor Commented:
Hi johnsone,

Are there actually rows in the TEMP table to update?
Yes, the member_id exists in the temp table.

Sorry, but what you have posted so far is still confusing to me as to which fields you need and where they come from.
The TEMP table contains member_id, country and email1. Country and email1 both come from the address table...
temp.member_id > address.member_id > orders.member_id

I'm also still confused as to why you need to join to the ORDERS table.
I just need to make sure that the given row temp.member_id has actually placed an order. If it hasn't, then I don't want to update it.

Your reply about using row_number() and partition by is new to me. I just read about it and this looks like it'd work. One thing, when I try to run your SQL, I'm getting a missing parentheses.

Again, thank you for your help!
0
 
johnsoneSenior Oracle DBACommented:
This might be it.  I don't have your tables, so I'm just writing the SQL, without the tables, I cannot really test it.
UPDATE temp t 
SET 
       ( 
              countrya, 
              email1a 
       ) 
       = 
       ( 
              SELECT country, 
                     email1 
              FROM   ( 
                              SELECT   a.country, 
                                       a.email1, 
                                       row_number() OVER (parition BY member_id ORDER BY address_id DESC) rn
                              FROM     address a 
                              WHERE    a.member_id = t.member_id 
                              AND      EXISTS 
                                       ( 
                                              SELECT 1 
                                              FROM   orders o 
                                              WHERE  o.member_id = a.member_id 
                                              AND    o.status IN ('M', 
                                                                  'C')) 
                              WHERE    rn = 1));

Open in new window

0
 
biglarrrrAuthor Commented:
Hi johnsone,

Thank you again. I also found that it was missing an extra closing parentheses after the "'C'))" Here is what I have now:

UPDATE temp
SET
       (
              country,
              email1
       )
       =
       (
              SELECT country,
                     email1
              FROM   (
                              SELECT   a.country,
                                       a.email1,
                                       row_number() OVER (PARTITION BY a.member_id ORDER BY a.address_id DESC) rn
                              FROM     address a
                              WHERE    a.member_id = t.member_id
                              AND      a.status = 'P'
                              AND      a.selfaddress = 1
                              AND      EXISTS
                                       (
                                              SELECT 1
                                              FROM   orders o
                                              WHERE  o.member_id = a.member_id
                                              AND    o.status IN ('M',
                                                                  'C')
                                        )
                      )
              WHERE    rn = 1);

The thing is, now I'm getting a "ORA-00904: "T"."MEMBER_ID": invalid identifier".

Thanks,
Larry

Thanks,
Larry
0
 
johnsoneSenior Oracle DBACommented:
You are missing an alias on the first line.  It should be:

UPDATE temp t
0
 
biglarrrrAuthor Commented:
Hi johnsone,

Sorry, I copy/pasted it wrong. I did have UPDATE temp t...

UPDATE temp t
SET
       (
              t.country,
              t.email1
       )
       =
       (
              SELECT country,
                     email1
              FROM   (
                              SELECT   a.country,
                                       a.email1,
                                       row_number() OVER (PARTITION BY a.member_id ORDER BY a.address_id DESC) rn
                              FROM     address a
                              WHERE    a.member_id = t.member_id
                              AND      EXISTS
                                       (
                                              SELECT 1
                                              FROM   orders o
                                              WHERE  o.member_id = a.member_id
                                              AND    o.status IN ('M',
                                                                  'C')
                                        )
                      )
              WHERE    rn = 1);

I'm getting "ORA-00904: "T"."MEMBER_ID": invalid identifier"
0
 
biglarrrrAuthor Commented:
Hi johnsone,

Just wondering what you think about doing something like this?

merge into temp t
using address a
on (t.member_id = a.member_id)
when matched then
update set t.country = a.country,
           t.email1 = a.email1,
           t.order_placed = 1
where a.status = 'P'
 and a.selfaddress = 1
 AND EXISTS (SELECT 1
             FROM   orders o
             WHERE  o.member_id = a.member_id
             AND    o.status IN ('M','C'))

Thanks,
Larry
0
 
biglarrrrAuthor Commented:
Thanks for all your help johnsone. I ended up using the merge. Completely understand your comment about using a merge with a temp table, but for my case, it seemed to be the cleanest way to get this to work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.