CODEWITHSUNDEEP
sqlsql-server
Sin5k4
Sin5k4

Reputation: 1626

Updating duplicate values;

I have a table which contains unique indexes;lets say

A B
1 1
1 2
1 3
1 4

And i want to update the B column,since sql updates them one by one if it tries to update the first column to 2 for ex i get::

A B
1 2
1 2
1 3
1 4

And as a result i would get two duplicate values at the 1st and 2nd row and of course an error message.

And i should update the table like that:

A B
1 2
1 1
1 3
1 4

So whats the course of action i should follow in case of this scenario?

Regards.

Maybe i should update the question abit: What if i wanted to change the b column completely; such as:

A B
1 4
1 2
1 3
1 1

Upvotes: 0

Views: 164

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

The solution is to do the swap in a single statement:

UPDATE YOUR_TABLE
SET B = (CASE B WHEN 1 THEN 2 ELSE 1 END)
WHERE A = 1 AND B IN (1, 2)

--- EDIT ---

To update several rows at once, you can do a JOIN-update from the temporary table:

CREATE TABLE #ROWS_TO_UPDATE (
    A int,
    B int,
    NEW_B int,
    PRIMARY KEY (A, B)
);

INSERT INTO #ROWS_TO_UPDATE (A, B, NEW_B) VALUES (1, 1, 4);
INSERT INTO #ROWS_TO_UPDATE (A, B, NEW_B) VALUES (1, 2, 3);
INSERT INTO #ROWS_TO_UPDATE (A, B, NEW_B) VALUES (1, 3, 2);
INSERT INTO #ROWS_TO_UPDATE (A, B, NEW_B) VALUES (1, 4, 1);

UPDATE YOUR_TABLE
SET B = NEW_B
FROM
    YOUR_TABLE JOIN #ROWS_TO_UPDATE
        ON YOUR_TABLE.A = #ROWS_TO_UPDATE.A AND YOUR_TABLE.B = #ROWS_TO_UPDATE.B;

DROP TABLE #ROWS_TO_UPDATE;

The above code transforms the following data...

A B
1 1
1 2
1 3
1 4

...to this:

A B
1 4
1 2
1 3
1 1

Upvotes: 0

JotaBe
JotaBe

Reputation: 39004

If I understand well, you have a primary key composed of two columns, and you want to swap the two first rows' PK.

If you don't have foreing keys which refers to this primary key, simply change one of the keys to a temporary unused value:

A B
1 10000
1 2

then change the second row:

A B
1 10000
1 1

Finally, change the first one:

A B
1 2
1 1

If you have objects depending on this primary key, you would have to make a copy of the other columns (for example the other columns of 1 1) to a "temporary row", copy the data of the second (1 2) to the first (1 1) and finally copy the "temporary row" to the second (1 2)

This all dependes on how and what you're exactly trying to do. Is it a stored procedure, is it a query... You should show more context.

You could apply this technique to an unlimited number of rows. You can also create a temporary table with key equivalences, and update your table from tha temporary table. So, it will be done in an atomic operation, and it won't violate the PK.

create table T
(A int, B int, C char(5),
primary key (A,B))

insert into T values(1,1,'first')
insert into T values(1,2,'secon')
insert into T values(1,3,'third')

create table #KeyChanges
(A int, B int, newA int, newB int)

insert into #KeyChanges values(1,1,1,3)
insert into #KeyChanges values(1,2,1,1)
insert into #KeyChanges values(1,3,1,2)

update T set T.A = KC.newA, T.B = KC.newB
from T
left join #KeyChanges as KC on T.A = KC.A and T.B = KC.B

Upvotes: 0

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

Try this

UPDATE tbl
SET B = 3 - B
WHERE A = 1 AND B IN (1, 2)

Or, generaly, you can use something like that:

UPDATE tbl
SET B = CASE B 
        WHEN 1 THEN 2 
        WHEN 2 THEN 1 
    END
WHERE A = 1 AND B IN (1, 2)

Another way:
add column C
through your loop fill C column with new values
update field B from C:

UPDATE tbl
SET B = C

Upvotes: 3

Related Questions