An update based on a query 1

[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

 

[gmmastros]

The basic structure is...

Update TableToUpdate
Set    TableToUpdate.Column1 = TableUpdatingFrom.Column1,
       TableToUpdate.Column2 = TableUpdatingFrom.Column2
From   TableToUpdate
       Inner Join TableUpdatingFrom
          On TableToUpdate.Id = TableUpdatingFrom.Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cbh35711]

Exactly what i needed. Thanks gmmastros.

Chris

 

[cbh35711]

Or so i thought...
So i've modified what you sent me and i'm getting an error when i try to run it.

update indirect_customer
set indirect_customer.name = unknown_names.name,
    indirect_customer.address1 = unknown_names.address1,
    indirect_customer.address2 = unknown_names.address2,
    indirect_customer.city = unknown_names.city,
    indirect_customer.state = unknown_names.state,
    indirect_customer.zipcode = unknown_names.zip
from indirect_customer inner join unknown_names
on indirect_customer.industry_id = unknown_names.dea

It says that the SQL command isn't properly ended.
Any idea what i did wrong?

Thanks again,

Chris

 

[gmmastros]

Are you using Microsoft SQL Server?

Run this query...

exec master..xp_msver

post the results back here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cbh35711]

That may be the problem. I'm using Oracle SQL Developer. That statement didn't work either.

Thanks again,

Chris

 

[gmmastros]

This forum is specifically for SQL Server. The oracle forum is: forum1177


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom