CODEWITHSUNDEEP
sql-server
user187169
user187169

Reputation:

Update multiple rows in SQL Server

My table looks like this:

c1 c2 c3
a   1 2000
a   2 2001
a   3 3000
a   4 3001
a   5 3002

Step 1: delete rows where c3 = 2000, 2001

Delete table where c3 like '2___'

Step 2: change 3000 to 2000, 3001 to 2001, 3002 to 2002, and so forth

I'm stuck here. I'd appreciate any pointers or examples.

Upvotes: 0

Views: 1171

Answers (3)

Christian Hayter
Christian Hayter

Reputation: 31071

Assuming your C3 column is a string (you don't say):

delete MyTable where c3 like '2%'
update MyTable set c3 = '2' + substring(c3, 2, len(c3) - 1) where c3 like '3%'

Assuming your C3 column is an integer:

delete MyTable where c3 between 2000 and 2999
update MyTable set c3 = c3 - 1000 where c3 between 3000 and 3999

Upvotes: 4

KM.
KM.

Reputation: 103579

try:

DELETE yourtable where c3 in (2000,2001)

UPDATE yourtable
    set C3=C3-1000 
    WHERE  c3>=3000 AND c3<4000

Upvotes: 0

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50712

  1. DELETE tablename WHERE c3 in (2000, 2001)

    DELETE tablename WHERE c3 LIKE '2%'

  2. UPDATE tablename SET c3 = c3 - 1000

Upvotes: 2

Related Questions