update query based on query from another table

[cbh35711]

I'm fairly new to sql, recently took some classes and putting the knowledge to use for my work.
I've got two tables with a common id.
One table is missing some values, name and address, that are in the second table.
Right now I've got a query that lists the common id and the correct names and addresses from the second table, but I'm not sure how to get them to replace the unknown values in the first table.

select dea,
       membername as Name,
       bill_to_address_1 as Address1,
       bill_to_address_2 as Address2,
       bill_to_city as City,
       bill_to_state as State,
       bill_to_zip as Zip,
       Plan
    from gpo_memberships
where dea = any
(SELECT industry_id FROM indirect_customer where name = 'UNKNOWN')
order by plan

I just can't seem to find an example to start off from.

Thank you for your assistance,

Chris

 

[cbh35711]

So I've written a different query...

update indirect_customer b
set (b.name, b.address1, b.address2, b.city, b.state, b.zipcode) =
(select a.name, a.address1, a.address2, a.city, a.state, a.zip 
from unknown_names a where a.dea = b.industry_id)

It updates the values from second table to the first table just fine, the problem is it replaces all the values that don't share the common id a null value. So since the second table only has the values i want to replace the first table ends up being mostly empty.
Any ideas what I'm doing wrong?

Thanks,

Chris

 

[SantaMufasa]

Chris,

Since you have no WHERE condition on the UPDATE statement itself, it implies to SQL that you want every row updated, and since there are no matches on many/most of your rows, you end up with NULLs in the target UPDATE columns.

Therefore, we usually devise/contrive a WHERE statement for the UPDATE to limit the updates to just the rows with matching values. Here is an example that I infer from your code:

update indirect_customer b
set (b.name, b.address1, b.address2, b.city, b.state, b.zipcode) =
(select a.name, a.address1, a.address2, a.city, a.state, a.zip 
from unknown_names a where a.dea = b.industry_id)
 [b]WHERE exists (select 'yada' from unknown_names a
                where a.dea = b.industry_id);[/b]

Let us know if this improves your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

Ahhhh....that makes things much better.

Thanks for your help Dave,

Chris