Query Verification - Reverting columns to old values 1

[Borvik]

I'm designing a table structure that at times columns need to revert to their "original" values.

This is for a customer table that is assigned to a company, once assigned to a company the values change to that of the companies values, and when the association is removed they need to revert back.

Here is part of the table structure

Table: customer
customerid (PK, bigint, not null)
contractid (int, not null)
origcontractid (int, null)
salesrepid (int, null)
origsalesrepid (int, null)
companyid (int, null)

For a mass update reverting customers back to their original (like when a company is removed from the database) I'm thinking of a query like:

UPDATE customer SET contractid = origcontractid, origcontractid = NULL, salesrepid = origsalesrepid, origsalesrepid = NULL, companyid = NULL WHERE companyid = 2

I ran it and it seemed to work just fine, but I wanted to verify that the query wouldn't cause any problems in and of itself.

I am assuming that it will work fine, but if I had the order of operations set up differently (such as setting origcontractid to null before setting the contractid to the origcontractid) then I could end up with problems (such as two nulls when only the original should turn null).

Am I correct in my thinking?

Thanks.

 

[gmmastros]

if I had the order of operations set up differently

It doesn't matter. It would still work.

Take a look at this code...

[green]-- Create a table var to store some dummy data[/green]
Declare @Temp Table (ContractId int, OrigContractId Int)

[green]-- load some dummy data in to the table[/green]
Insert Into @Temp Values(1,2)
Insert Into @Temp Values(3,null)
Insert Into @Temp Values(null,4)

[green]-- Show the original data[/green]
Select * From @Temp

[green]-- Swap the data in the columns[/green]
Update @Temp 
Set    [red]ContractId[/red] = [blue]OrigContractId[/blue], 
       [blue]OrigContractId[/blue] = [red]ContractId[/red]

[green]-- Show the swapped data[/green]
Select * From @Temp

[green]-- Swap the data again (changing the order).[/green]
Update @Temp 
Set    [blue]OrigContractId[/blue] = [red]ContractId[/red], 
       [red]ContractId[/red] = [blue]OrigContractId[/blue]

[green]-- Show the data again[/green]
Select * From @Temp

Copy/Paste that to a query window and run it. You'll see that the order you list the columns does not matter.



-George

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

 

[Borvik]

Thanks for confirming it will work! It's nice to know the order doesn't necessarily matter in SQL.

Thanks again.

 

[gmmastros]

It's funny...

Just yesterday, a couple hours before you posted your question, I got a question from one of the support people at the company where I work.

She said, "All of the one way streets in the map are pointing in the wrong direction". I said to her, "You took a SQL class, write a query to fix it (and don't ask for help from anyone else). Let me know what you come up with before you run it". 20 Minutes later, she showed me 1/2 a query.

I responded with...

Update MapStreets 
Set    SpeedLimit = ReturnSpeedLimit, 
       ReturnSpeedLimit = SpeedLimit, 
       TravelTime = ReturnTravelTime, 
       ReturnTravelTime = TravelTime 
Where  SpeedLimit <> ReturnSpeedLimit
       And (SpeedLimit = 0 or ReturnSpeedLimit = 0)

I was talking with another developer later about it. He told me he would have created a column (temporarily) in the table and then did the old 'procedural swap'.

Another developer would have create a temp table.

There are lots of ways to solve this problem, but none of them are as good as the method I showed.


-George

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