Update Two Tables based on join

[dldev]

Hello,

I have this query which is pulling from a production table:

select 
	o.OrderNumber as [Order Number],	
    o.CustomerID as [Customer ID],		
	o.LastName + ' ' + o.FirstName as [Customer Name],
	c.Email,
	c.AdditionalEmail as [Additional Email], 
	c.Phone,
	o.ShippingCity as [Shipping City], 
	o.ShippingState as [Shipping State],
    o.ShippingZip as [Shipping Zip],
	o.ShippingCountry as [Shipping Country], 
	o.OrderDate as [Order Date],			
    o.SerialNumbers as [Serial Number],		
	o.ReorderSerialNumbers as [Reorder Serial Number]
from 
	orders o WITH (NOLOCK) inner join customer c ON o.CustomerID = c.CustomerID

And this query which is pulling from a temp table:

select 
	Number as [Order Number],		
	OEM_ID as [Customer ID],		
	DealerName as [Customer Name],
	DealerEmail as [Email], 
	'' as [Additional Email], 
	DealerPhoneNumber as [Phone],
	DealerCity as [Shipping City],
	[State] as [Shipping State],
	Zip as [Shipping Zip],
	'United States' as [Shipping Country],
	'2007-12-26' as [Order Date],
    '12345' as [Serial Number],			
	'12346' as [Reorder Serial Number]						
from EMPS3_temp

What is the best way to update the production table with the data from the temp table? The key field (unfortunately) is the field. I was think maybe something with a union all join, or a cascading update, but I'm not sure what the best approach is and how to write it. Any help would be greatly appreciated.

Thank you,
Dennis

 

[SQLSister]

First, email is an extremely bad choice for a primary key as people change their emails. And emails can get reused once the have gone unused. So John Smith leaves copmany a and month later Judy Smith is hired. The likelihood is that they will have the same email address (jSmith@mycompany.com) even though they are two different people. This will sooner or later cause data integrity issues. It is urgent that you fix this.

But to your question, you can easily update from the data in another table by doing a join in the update. The genreal format is

update a
set field1 = b.field1
from tablea a 
join tableb b on a.idfield = b.idfield

"NOTHING is more important in a database than integrity." ESquared

 

[r937]

It is urgent that you fix this.

actually it isn't urgent at all


it is important, but not urgent

it's like the difference between air and food

if you're missing both, then it is urgent to find air, and important to find food





r937.com | rudy.ca

 

[ca8msm]

What happens if the scenario that SQLSister pointed out happens today? It's too late by then if it hasn't been fixed now. Does that make it urgent?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

According to

http://dictionary.reference.com/browse/urgent

1. compelling or requiring immediate action or attention; imperative; pressing: an urgent matter.

Personally, I think that if known data integrity issues exist, then it is urgent that it is fixed.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

does that make it urgent? no, because the actual update hasn't been performed yet

you guys just love splitting hairs, dontcha

you know what i mean (or if you don't, good luck with your long term careers) and so does your manager

urgent is urgent, important is important

something can be both urgent and important, but John Smith's email being replaced by Judy Smith's email is neither

make a note of it, perform the update, do a quick integrity check, and then put in the request to have the production schema completely torn down and rebuilt with different primary keys

your manager will tell you how urgent that is




r937.com | rudy.ca

 

[PatriciaObreja]

Please try:

Update orders
set ColumnName =
(select ColumnName
from EMPS3_temp
where EMPS3_temp.Email = orders.Email)

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[SQLSister]

I used urgent deliberately. It is far more important than the update he wants to do because a database without data integrity is completely useless and in fact may be counterproductive. A data integrity issue should stop everything else and be fixed first. Managers make bad decisions with bad data and clients often leave a company when the bad data issue becomes apparent due to emails being sent to the wrong person for instance. A data integrity issue costs the company money and reputation two things you do not want to be the cause of if you value your career.

"NOTHING is more important in a database than integrity." ESquared

 

[r937]

i agree with you about integrity

now march right into the manager's office and tell him/her that all other work must stop until we redesign the table so that it does not use email as the primary key

like i said, there is a big different between urgent and important

can we hear from some managers please? we already know how DBAs feel about this issue



r937.com | rudy.ca

 

[SQLSister]

Where I work we have many clients and there is a very high visibilty whenever a data integrity issue comes up because our dat is so very visible to the clients. It is true that some data integrity issues may be more urgent than others. Belive me when we had a couple of bad imports due to bad data sent in from the client and users could no longer sign in, everything stopped until it was fixed. If we had a design issue that causes a problem like this, all other work does stop (well for the person assigned to the fix of course) until it is fixed because it is that important. But our managers are quite attuned to the fact that these types of problems cause clients to go elsewhere.

I've never had difficulty convincing a manager to stop everything to fix an urgent issue. You do need to explain to them in business terms (ie loss of revenue) why it is important and urgent. It also helps when you have a track record for being right when you bring up an issue.

It is true that some issues may be less urgent, but email is one of the ones that is most likely to become visible to the client and cause loss of business. That is one reason why fixing it is urgent. Plus we all know that the longer a data integrity issue is allowed to continue the harder it becomes to fix.

"NOTHING is more important in a database than integrity." ESquared

 

[dldev]

Thank you all VERY much for your valuable input. I will take your advice all the way around and hope I can contribute to this great message board in return. I will try the query and let you know.
Dennis

 

[dldev]

Hello all,

Uh, I see where from your example I can update one table, but in this case I need to update two tables which are involved with the join for this query.

Could someone please show me how I could update BOTH table in one update statement? Would this involve some use of a cascading update? Maybe I am not completely understanding, and if so please forgive me.

Thanks,
Dennis

 

[PatriciaObreja]

You want to update 2 tables with data from one table? Please show us some sample data.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[jbenson001]

You can't update 2 tables with one UPdate statement, you will need to write 2 update statements.

 

[gmmastros]

>> you will need to write 2 update statements. , and wrap the 2 update statements in to a transaction.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dldev]

Hello,

But I cannot write two statements in this case because the key field(email) only resides in the customers table, and the query I sent involves a join between orders and customers tables. Here are the queries again and the required resultset:

I have this query which is pulling from a production table:

select 
    o.OrderNumber as [Order Number],    
    o.CustomerID as [Customer ID],        
    o.LastName + ' ' + o.FirstName as [Customer Name],
    c.Email,
    c.AdditionalEmail as [Additional Email], 
    c.Phone,
    o.ShippingCity as [Shipping City], 
    o.ShippingState as [Shipping State],
    o.ShippingZip as [Shipping Zip],
    o.ShippingCountry as [Shipping Country], 
    o.OrderDate as [Order Date],            
    o.SerialNumbers as [Serial Number],        
    o.ReorderSerialNumbers as [Reorder Serial Number]
from 
    orders o WITH (NOLOCK) inner join customer c ON o.CustomerID = c.CustomerID

And this query which is pulling from a temp table:

select 
    Number as [Order Number],        
    OEM_ID as [Customer ID],        
    DealerName as [Customer Name],
    DealerEmail as [Email], 
    '' as [Additional Email], 
    DealerPhoneNumber as [Phone],
    DealerCity as [Shipping City],
    [State] as [Shipping State],
    Zip as [Shipping Zip],
    'United States' as [Shipping Country],
    '2007-12-26' as [Order Date],
    '12345' as [Serial Number],            
    '12346' as [Reorder Serial Number]                        
from EMPS3_temp

Here is the required resultset:
order#cust#custname email  phone      city st zip   orddate 
5361  8840 ABC,INC ABC.COM 9182455926 TEST OK 12312 1/1/08

as you can see, some fields come from the orders table, some come from the customer table.

Thanks,
Dennis

 

[dldev]

George:
"you will need to write 2 update statements. , and wrap the 2 update statements in to a transaction"

Could you please show me an example of how I would do this?

Thanks,
Dennis

 

[gmmastros]

I encourage you to read this:

http://www.4guysfromrolla.com/webtech/080305-1.shtml

The example shows transaction handling for delete statements, whereas you are updating, but that doesn't matter. The point is.... If one of your update statements fails, then you want the whole thing to be rolled back.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dldev]

Thanks George! I'll check it out.
Dennis