• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

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
0
biglarrrr
Asked:
biglarrrr
  • 7
  • 5
1 Solution
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
johnsoneSenior 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
 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now