Swap field values in UPDATE

7 Sep 2006 1:13 PM

David

I would like to be able to update customer and insurance dollar values in
one pass using the UPDATE. Below is my code so far for the update but it
only updates the Insurance columns. For example, what I want to accomplish
is to make InsuranceAmount = CustomerAmount and make CustomerAmount =
InsuranceAmount, etc. Any help would be appreciated.

UPDATE dbo.RepairOrderRates
SET InsuranceAmount = CustomerAmount,
InsuranceUnits = CustomerUnits,
InsuranceEstimate = CustomerEstimate,
InsTaxable = CustTaxable
FROM dbo.RepairOrder
WHERE (dbo.RepairOrder.RecordID = dbo.RepairOrderRates.RecordID)
AND (dbo.RepairOrder.PayorIs = 'C')
AND (dbo.RepairOrder.CustomerID IN (1345, 2895, 11051))

David

7 Sep 2006 1:27 PM

SQL Menace

If I understood correctly, then this will do what you want

UPDATE dbo.RepairOrderRates
SET InsuranceAmount = CustomerAmount,
InsuranceUnits = CustomerUnits,
InsuranceEstimate = CustomerEstimate,
InsTaxable = CustTaxable ,
CustomerAmount = InsuranceAmount,
CustomerUnits = InsuranceUnits,
CustomerEstimate = InsuranceEstimate,
CustTaxable =InsTaxable
FROM dbo.RepairOrder
WHERE (dbo.RepairOrder.RecordID = dbo.RepairOrderRates.RecordID)
AND (dbo.RepairOrder.PayorIs = 'C')
AND (dbo.RepairOrder.CustomerID IN (1345, 2895, 11051))

Denis the SQL Menace
http://sqlservercode.blogspot.com/

David wrote:
Show quote

> I would like to be able to update customer and insurance dollar values in
> one pass using the UPDATE. Below is my code so far for the update but it
> only updates the Insurance columns. For example, what I want to accomplish
> is to make InsuranceAmount = CustomerAmount and make CustomerAmount =
> InsuranceAmount, etc. Any help would be appreciated.
>
> UPDATE dbo.RepairOrderRates
> SET InsuranceAmount = CustomerAmount,
> InsuranceUnits = CustomerUnits,
> InsuranceEstimate = CustomerEstimate,
> InsTaxable = CustTaxable
> FROM dbo.RepairOrder
> WHERE (dbo.RepairOrder.RecordID = dbo.RepairOrderRates.RecordID)
> AND (dbo.RepairOrder.PayorIs = 'C')
> AND (dbo.RepairOrder.CustomerID IN (1345, 2895, 11051))
>
> David

7 Sep 2006 2:27 PM

David

Yes. I wasn't sure when the actual "update" occurred as I was afraid that
when the "customer" columns were updated that they would pick up the
original insurance amount or the new insurance amount (which would put them
back to where they were).

David
Show quote

"SQL Menace" <denis.g***@gmail.com> wrote in message
news:1157635672.055693.265830@m79g2000cwm.googlegroups.com...
> If I understood correctly, then this will do what you want
>
> UPDATE dbo.RepairOrderRates
> SET InsuranceAmount = CustomerAmount,
> InsuranceUnits = CustomerUnits,
> InsuranceEstimate = CustomerEstimate,
> InsTaxable = CustTaxable ,
> CustomerAmount = InsuranceAmount,
> CustomerUnits = InsuranceUnits,
> CustomerEstimate = InsuranceEstimate,
> CustTaxable =InsTaxable
> FROM dbo.RepairOrder
> WHERE (dbo.RepairOrder.RecordID = dbo.RepairOrderRates.RecordID)
> AND (dbo.RepairOrder.PayorIs = 'C')
> AND (dbo.RepairOrder.CustomerID IN (1345, 2895, 11051))
>
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> David wrote:
>> I would like to be able to update customer and insurance dollar values in
>> one pass using the UPDATE. Below is my code so far for the update but it
>> only updates the Insurance columns. For example, what I want to
>> accomplish
>> is to make InsuranceAmount = CustomerAmount and make CustomerAmount =
>> InsuranceAmount, etc. Any help would be appreciated.
>>
>> UPDATE dbo.RepairOrderRates
>> SET InsuranceAmount = CustomerAmount,
>> InsuranceUnits = CustomerUnits,
>> InsuranceEstimate = CustomerEstimate,
>> InsTaxable = CustTaxable
>> FROM dbo.RepairOrder
>> WHERE (dbo.RepairOrder.RecordID = dbo.RepairOrderRates.RecordID)
>> AND (dbo.RepairOrder.PayorIs = 'C')
>> AND (dbo.RepairOrder.CustomerID IN (1345, 2895, 11051))
>>
>> David
>