Solved

Update table using multiple table qualifications

Posted on 2016-09-12
12
36 Views
Last Modified: 2016-09-15
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
Comment
Question by:biglarrrr
  • 7
  • 5
12 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 41794868
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
 

Author Comment

by:biglarrrr
ID: 41794875
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
 

Author Comment

by:biglarrrr
ID: 41794880
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
 
LVL 34

Expert Comment

by:johnsone
ID: 41794914
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
 

Author Comment

by:biglarrrr
ID: 41794943
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
 
LVL 34

Expert Comment

by:johnsone
ID: 41794967
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:biglarrrr
ID: 41795144
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
 
LVL 34

Expert Comment

by:johnsone
ID: 41795250
You are missing an alias on the first line.  It should be:

UPDATE temp t
0
 

Author Comment

by:biglarrrr
ID: 41795308
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
 

Author Comment

by:biglarrrr
ID: 41795332
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
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 41795822
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
 

Author Closing Comment

by:biglarrrr
ID: 41800330
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Syntax 8 57
levels for reporting 5 51
oracle query 15 63
Identify records which do NOT qualify for a view 9 29
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now