Closed SQL SERVER DATA BASE

[rex fred]

Hello guys sino d2 mga programmers? yung experts sa database
Pano po ba mag auto arrange or adjust halimbawa yung ID number?
Halimbawa:
1
2
3
4
5
tapos binura ko yung number 3
pano cya i auto arrage na maging 1234 nlng, Pero yung naka Increment po cya?

alam ko po pano gawin sa mga programming language pero di ko alam pano gawin sa sql server databse hehe gus2 ko kasi malaman.

thanks po sa mkakasagot

 

[_neelaeg]

Gusto niyo po bang mangyari is kapag binura mo ung data ng #3 is automatic na 1,2,3,4 na ung lalabas?

 

[rex fred]

Gusto niyo po bang mangyari is kapag binura mo ung data ng #3 is automatic na 1,2,3,4 na ung lalabas?

Yes po

 

[rex fred]

ni research kona po cya kaso wala akong makita, wla pa cguro nka gawa ng tutorial or di talaga pwd yun

 

[_neelaeg]

ni research kona po cya kaso wala akong makita, wla pa cguro nka gawa ng tutorial or di talaga pwd yun

In-auto increment mo ba und ID? Nung nag create ka ng table?

 

[rex fred]

In-auto increment mo ba und ID? Nung nag create ka ng table?

yes sir, kasi pag hnd cya nka auto increment manual yung pag iinput ng ID number

 

[_neelaeg]

yes sir, kasi pag hnd cya nka auto increment manual yung pag iinput ng ID number

Try mo po if this will work. Although I'm not really sure if pwede.

SET num := 0;
UPDATE your_table SET id = num := (@num+1);
ALTER TABLE tableName AUTO_INCREMENT = 1;

 

[rex fred]

Try mo po if this will work. Although I'm not really sure if pwede.

SET num := 0;
UPDATE your_table SET id = num := (@num+1);
ALTER TABLE tableName AUTO_INCREMENT = 1;

cge po try ko mamaya, thank you po master

 

[akositito]

Search for "tsql row_number()"

 

[N1ghtmare]

Try this query kung example may nadelete ka isang column row and need mo ibalik yun ID number in an order count.

set @autoid :=0;  update table_name set id = @autoid := (@autoid+1);
alter table table_name Auto_Increment = 1;

 

[aa1012]

Attainable yan through manually altering your table via tsql command. Pero mawawala yung data integrity mo if you do that. What is the purpose of doing it? bakit mo kaylangan ireorder yung ID e yun nga yung identity ng bawat row mo(unless you have other unique field). so for example:

table before deletion:
1 data1
2 data2
3 data3 -Assuming this will be deleted
4 data4

table after deletion:
1 data1
2 data2
3 data4

Imagine data4 is a product item and was used to be set to 4 as its unique key or field and then suddenly reassigning to 3 tapos may mga table ka na naka associate sa kanya something like order detail, magkakaron ka ng orphaned data nyan. at hindi yan papasa sa standard ng isang normalized schema at tables na may relationship sa ibang table as constraints. And in the first place, pag inapply mo yan through sql at may table constraints ka, dun pa lang may error ka na pag mag reorder ka ng ID. Pero im not saying na hindi yan attainable, its easy to do it pero im giving advice and fyi to do it with caution lalo na sa production databases laking gulo nyan sa data mo.

 

[aa1012]

Search for "tsql row_number()"

ROW_NUMBER ( )  
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

This is a handy tool when querying data and you want to number it sequentially but its effect cannot be applied to the table itself. Its only in your query result.

What op is trying to achieve is to reassign ID numbers whenever something is remove which is I think a bad idea.

 

[rex fred]

Attainable yan through manually altering your table via tsql command. Pero mawawala yung data integrity mo if you do that. What is the purpose of doing it? bakit mo kaylangan ireorder yung ID e yun nga yung identity ng bawat row mo(unless you have other unique field). so for example:

table before deletion:
1 data1
2 data2
3 data3 -Assuming this will be deleted
4 data4

table after deletion:
1 data1
2 data2
3 data4

Imagine data4 is a product item and was used to be set to 4 as its unique key or field and then suddenly reassigning to 3 tapos may mga table ka na naka associate sa kanya something like order detail, magkakaron ka ng orphaned data nyan. at hindi yan papasa sa standard ng isang normalized schema at tables na may relationship sa ibang table as constraints. And in the first place, pag inapply mo yan through sql at may table constraints ka, dun pa lang may error ka na pag mag reorder ka ng ID. Pero im not saying na hindi yan attainable, its easy to do it pero im giving advice and fyi to do it with caution lalo na sa production databases laking gulo nyan sa data mo.

Thank you po master, naisip ko lng po eh, tska di tlaga pwd yun kasi kapag na hard copy na yung mga ID ng employee w/ID number tapos buburahin ko halimbawa data # 3 maiiba yung mga ID # nanaman na nka hard copy, hustle nanaman... gumagawa kasi aq ng Human Resources Management system.... Pero yung Automatic na mag Sequence yung mga number sa sql database Di ko tlga alam pano yun .. Anyway thank you po master

 

[aa1012]

Thank you po master, naisip ko lng po eh, tska di tlaga pwd yun kasi kapag na hard copy na yung mga ID ng employee w/ID number tapos buburahin ko halimbawa data # 3 maiiba yung mga ID # nanaman na nka hard copy, hustle nanaman... gumagawa kasi aq ng Human Resources Management system.... Pero yung Automatic na mag Sequence yung mga number sa sql database Di ko tlga alam pano yun .. Anyway thank you po master

saan mo ba gagamitin yung auto sequence na number? sa report ba or some kind of algorithm sa code mo? what are you trying to do with it? kasi kung query lang you can use row_number function or pwede subquery. Ang daming ways para ma attain yan.

 

[rex fred]

saan mo ba gagamitin yung auto sequence na number? sa report ba or some kind of algorithm sa code mo? what are you trying to do with it? kasi kung query lang you can use row_number function or pwede subquery. Ang daming ways para ma attain yan.

Sa defense po master, baka sakaling tanungin aq ng mga panel may panampal aq sa mga muka nila hehe

 

[sahminan1]

what is
1
2
3
4
5
aalisin mo ang 4 so auto magiging
1
2
3
4
pero ang value or laman ng 4 is erase and ang 5 na ang nasa 4..
if alam mo ang gagawin through conversation pwde mo ring gawin via sql
1. problem dito is di clear
wala kang post database sample
wala kang target if ano ba tlga ang gusto mo kasi di malinaw mas marami ang tanong
suggest post database or upload bak up then para ma test ng mga mag susugest then susubmit ang script para sa gusto mo.

 

[N1ghtmare]

Attainable yan through manually altering your table via tsql command. Pero mawawala yung data integrity mo if you do that. What is the purpose of doing it? bakit mo kaylangan ireorder yung ID e yun nga yung identity ng bawat row mo(unless you have other unique field). so for example:

table before deletion:
1 data1
2 data2
3 data3 -Assuming this will be deleted
4 data4

table after deletion:
1 data1
2 data2
3 data4

Imagine data4 is a product item and was used to be set to 4 as its unique key or field and then suddenly reassigning to 3 tapos may mga table ka na naka associate sa kanya something like order detail, magkakaron ka ng orphaned data nyan. at hindi yan papasa sa standard ng isang normalized schema at tables na may relationship sa ibang table as constraints. And in the first place, pag inapply mo yan through sql at may table constraints ka, dun pa lang may error ka na pag mag reorder ka ng ID. Pero im not saying na hindi yan attainable, its easy to do it pero im giving advice and fyi to do it with caution lalo na sa production databases laking gulo nyan sa data mo.

Agree with this statement. Why to set a unique ID kung marere-order at mawawala sa arrangement. I think magkakaroon nga ng problem when it comes to communicating na ng mga data specially if it's already associated with other set of tables.

 

[akositito]

ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

This is a handy tool when querying data and you want to number it sequentially but its effect cannot be applied to the table itself. Its only in your query result.

What op is trying to achieve is to reassign ID numbers whenever something is remove which is I think a bad idea.

Magagamit mo yan pangupdate ng lahat ng record (kung nakapag tsql ka na, gamit ka lang CTE bla bla blah)
I assume na hindi yung "primary ID" yung tinutukoy ni OP. I just give him a hint kung pano niya magagawa yung gusto niya. Anyway based sa comments niya mukang primary id ang gusto niya iupdate which is truly a bad idea.

 

[aa1012]

Magagamit mo yan pangupdate ng lahat ng record (kung nakapag tsql ka na, gamit ka lang CTE bla bla blah)
I assume na hindi yung "primary ID" yung tinutukoy ni OP. I just give him a hint kung pano niya magagawa yung gusto niya. Anyway based sa comments niya mukang primary id ang gusto niya iupdate which is truly a bad idea.

yes magagamit mo yan through the use of CTE or temp table or table variable pero sa concern ni op not a good idea.

 

[aa1012]

Sa defense po master, baka sakaling tanungin aq ng mga panel may panampal aq sa mga muka nila hehe

Nako, kung sa defense mo yan balak gamitin, unang-una kung marurunong yung mga prof mo sa reality ng data structure, hindi nila yan itatanong sayo kasi alam nilang hindi yan makatotohanan at lalabag yan sa norm at principle ng rdbms. Walang dev na gagawa ng ganyang concept.