Update increase ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update increase !

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2014-08-11 : 06:22:29

Hi all ,
I have a problem as , please give me advices ! Thank in advance !
I have seven records in table :
Id--Name-AId-PriCate-CateId-Ord
5---A----320---0-------3----1
6---E----300---1-------3----2
7---G----360---1-------6----9
8---M----370---1-------3----3
9---L----330---0------12----8
10---M----361---1-------3----4
11---Z----362---1-------3----5

Id is increase value,
My problem :

When i insert any record in CateId =3 with first position:
With first 100 records I want to update all other records with Ord field to 100,Other records I will set Ord=101 , Output will display as :

5---A----320---0-------3----1
6---E----300---1-------3----2
8---M----370---1-------3----3
7---M----361---1-------3----4
8---Z----342---1-------3----5
9---Z----862---1-------3----..
10---Z----362---1-------3----99
11---Z----122---1-------3----100
12---Z----562---1-------3----101
13---Z----752---1-------3----101
14---Z----322---1-------3----101
15---Z----372---1-------3----101
...

How can i do ?
Thank you very much !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-11 : 07:39:23

Your question is not very clear to me.

what do you mean by "insert any record in CateId =3 with first position" ?

quote:
With first 100 records I want to update all other records with Ord field to 100,Other records I will set Ord=101

Where is the first 100 records coming from ? You said you only have 7 records in that table.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-11 : 08:14:53

UPDATE dbo.Table1 SET Ord = Ord + 1 WHERE Cat = 3

Then you insert your new row with a value of 1 for Ord.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

pamyral_279
Posting Yak Master

143 Posts

Posted - 2014-08-11 : 21:55:49

quote:
Originally posted by SwePeso

UPDATE dbo.Table1 SET Ord = Ord + 1 WHERE Cat = 3

Then you insert your new row with a value of 1 for Ord.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank you KHtan and Swe so much !
For your solution, Ord will increase with no limit ! I want to bound Ord in [1 to 100] , other records will be Ord 101 !

Have you other ways ?
Thank you so much !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-12 : 02:04:01

UPDATE dbo.Table1 SET Ord = Ord + 1 WHERE Cat = 3
DELETE FROM dbo.Table1 WHERE Ord >= 101 AND Cat = 3

And now insert the new row.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

pamyral_279
Posting Yak Master

143 Posts

Posted - 2014-08-12 : 03:13:46

Thank you swe !
But i need to keep old records , just change Ord to 101 !

Output :
5---A----320---0-------3----1
6---E----300---1-------3----2
8---M----370---1-------3----3
7---M----361---1-------3----4
8---Z----342---1-------3----5
9---Z----862---1-------3----..
10---Z----362---1-------3----99
11---Z----122---1-------3----100
12---Z----562---1-------3----101
13---Z----752---1-------3----101
14---Z----322---1-------3----101
15---Z----372---1-------3----101

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-12 : 05:11:15

[code]UPDATE f
SET Ord = CASE
WHEN rn >= 101 THEN 101
ELSE 1 + rn
END
FROM (
SELECT Ord,
ROW_NUMBER() OVER (ORDER BY Ord) AS rn
FROM dbo.Table1
) AS f;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

pamyral_279
Posting Yak Master

143 Posts

Posted - 2014-08-12 : 05:51:57

quote:
Originally posted by SwePeso

UPDATE f
SET Ord = CASE
WHEN rn >= 101 THEN 101
ELSE 1 + rn
END
FROM (
SELECT Ord,
ROW_NUMBER() OVER (ORDER BY Ord) AS rn
FROM dbo.Table1
) AS f;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank swe million ! Seem that working fine ! I will check more !
Can i insert new record with any position in [1 .. 100] ? (Not first position in this way)

pamyral_279
Posting Yak Master

143 Posts

Posted - 2014-08-12 : 05:54:09

Thank swe million ! Seem that working fine ! I will check more !
Can i insert new record with any position in [1 .. 100] ? (Not first position in this way)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-12 : 06:50:25

Yes. In case of duplicate values for Ord, just add a second sort column in the windowed function.
And also, add a WHERE clause of Cat = 3 for the derived table f.

First insert, and then

UPDATE	f
SET	Ord =	CASE
			WHEN rn >= 101 THEN 101
			ELSE rn
		END
FROM	(
		SELECT	Ord,
			ROW_NUMBER() OVER (ORDER BY Ord, Name) AS rn
		FROM	dbo.Table1
		WHERE	Cat = 3
	) AS f;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

pamyral_279
Posting Yak Master

143 Posts

Posted - 2014-08-14 : 04:13:56

quote:
Originally posted by pamyral_279

quote:
Originally posted by SwePeso

UPDATE f
SET Ord = CASE
WHEN rn >= 101 THEN 101
ELSE 1 + rn
END
FROM (
SELECT Ord,
ROW_NUMBER() OVER (ORDER BY Ord) AS rn
FROM dbo.Table1
) AS f;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank swe million ! Seem that working fine ! I will check more !
Can i insert new record with any position in [1 .. 100] ? (Not first position in this way)

What happen if table has many rows (ex. 10000 records) ? Is this best way ?
Thank swe so much :)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 08:04:13

10000 rows is nothing. SwePeso's solution is probably the best you can do in any case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources