CODEWITHSUNDEEP
mysqlsqlsql-server
user5171795
user5171795

Reputation: 115

SQL - Copying certain rows in a table to the same table and changing their values

I have the following table in Microsoft SQL Server called CustomerInfo

BEFORE 

CustomerInfo

Type         CustomerId          CustParameter        Value
Inbound      StateLOCAL          LastName             Johnson
Inbound      StateLOCAL          ExchangeCode         ALI
Inbound      StateLOCAL          IsPriority           False
Inbound      CityLOCAL           LastName             Rogers
Inbound      CityLOCAL           ExchangeCode         RAR
Inbound      CityLOCAL           IsPriority           True
Inbound      TownBOUND           LastName             Brown
Inbound      TownBOUND           ExchangeCode         JSP
Inbound      TownBOUND           IsPriority           True

And I need to get it to the following table

AFTER

CustomerInfo

Type         CustomerId          CustParameter        Value
Inbound      StateLOCAL          LastName             Johnson
Inbound      StateLOCAL          ExchangeCode         ALI
Inbound      StateLOCAL          IsPriority           False
Inbound      CityLOCAL           LastName             Rogers
Inbound      CityLOCAL           ExchangeCode         RAR
Inbound      CityLOCAL           IsPriority           True
Inbound      TownBOUND           LastName             Brown
Inbound      TownBOUND           ExchangeCode         JSP
Inbound      TownBOUND           IsPriority           True
Inbound      CityBORDER          LastName             Rogers
Inbound      CityBORDER          ExchangeCode         RAR
Inbound      CityBORDER          IsPriority           True

The table is a lot bigger and I need it to do the following:

Upvotes: 2

Views: 46

Answers (2)

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

Try these SQL.

step1: Execute below SQL. Result is OK.

select Type,  REPLACE(ci.CustomerID, 'LOCAL', 'BORDER'), CustParameter, Value from customerInfo where customerId in (
select distinct CustomerId from customer_info where CustomerId like "%LOCAL%" and CustParameter = "IsPriority" and Value= "True"

)

step2: Execute below SQL. insert that record in to table.

INSERT INTO customerInfo (Type, CustomerId, CustParameter, Value)
    select Type, REPLACE(ci.CustomerID, 'LOCAL', 'BORDER'), CustParameter, Value from customerInfo where customerId in (
    select distinct CustomerId from customer_info where CustomerId like "%LOCAL%" and CustParameter = "IsPriority" and Value= "True"

    )

Thank you.

Upvotes: 1

JohnS
JohnS

Reputation: 2052

UPDATE
  ci
SET
  ci.CustomerID = REPLACE(ci.CustomerID, 'LOCAL', 'BORDER')
FROM
  CustomerInfo ci
WHERE
  ci.CustomerID IN ( SELECT DISTINCT
                      ci2.CustomerID
                     WHERE
                      ci2.CustomerID LIKE '%LOCAL%'
                      AND ci2.CustParameter LIKE '%IsPriority%'
                      AND ci2.Value = 'True' )

Upvotes: 0

Related Questions