none
Assigning sequential values to rows RRS feed

  • Question

  • Hi

    I have a query as below;

    SELECT Staff.PayrollReferenceNo
    FROM Staff 
    WHERE (Staff.PayrollReferenceNo = 0) Or (Staff.PayrollReferenceNo Is Null)
    ORDER BY Staff.StaffID

    How can I *UPDATE* PayrollReferenceNo column in the rows of this resultset with sequential numeric values starting from Max(PayrollReferenceNo) + 1?

    Thanks

    Regards



    • Edited by Y a h y a Tuesday, December 10, 2019 7:57 AM
    Tuesday, December 10, 2019 2:23 AM

Answers

  • If the order is not important, then check this too:

    declare @i int = ISNULL((select MAX(PayrollReferenceNo) from Staff), 0)

     

    update Staff

    set @i = PayrollReferenceNo = @i + 1

    where PayrollReferenceNo IS NULL or PayrollReferenceNo = 0

    Tuesday, December 10, 2019 8:24 PM

All replies

  • try this..
    Select (select max(PayRollReferenceno) from Staff)+ROW_number() over (order by StaffID), StaffID
    from staff
    where ((PayrollReferenceNo = 0) Or (PayrollReferenceNo Is Null)) 


    Hope it Helps!!


    • Edited by Stan210 Tuesday, December 10, 2019 6:31 AM
    Tuesday, December 10, 2019 6:29 AM
  • SELECT Staff.PayrollReferenceNo+1 AS NewNum
    FROM Staff 
    WHERE (Staff.PayrollReferenceNo = 0) Or (Staff.PayrollReferenceNo Is Null)
    ORDER BY Staff.StaffID DESC

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 10, 2019 6:50 AM
    Answerer
  • Hi ,

    Or please check this one .

    Select max(PayrollReferenceNo)over(partition by(select 1))+ROW_number() over (order by StaffID) ID, StaffID
    from staff
    where ((PayrollReferenceNo = 0) Or (PayrollReferenceNo Is Null)) 
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 10, 2019 8:00 AM
  • Hi

    May be I did not explain well. I want the PayrollReferenceNos that are 0 or NULL to be updated with sequential values starting with MAX(PayrollReferenceNos) + 1.

    Thanks

    Regards

    Tuesday, December 10, 2019 8:41 AM
  • Select Coalesce(Max(PayrollReferenceNos ), 0) + 1,
      <values you want in the other columns
    From Tab;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, December 10, 2019 8:44 AM
    Answerer
  • Hi

    May be I did not explain well. I want the PayrollReferenceNos that are 0 or NULL to be updated with sequential values starting with MAX(PayrollReferenceNos) + 1.

    Thanks

    Regards

    did you try my answer above. It should do what you mentioned

    Hope it Helps!!

    Tuesday, December 10, 2019 2:17 PM
  • If the order is not important, then check this too:

    declare @i int = ISNULL((select MAX(PayrollReferenceNo) from Staff), 0)

     

    update Staff

    set @i = PayrollReferenceNo = @i + 1

    where PayrollReferenceNo IS NULL or PayrollReferenceNo = 0

    Tuesday, December 10, 2019 8:24 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 26, 2019 3:03 AM
  • >> I have a query as below;

    SELECT payroll_reference_nbr

    FROM Staff

    WHERE payroll_reference_nbr = 0

    OR payroll_reference_nbr IS NULL;

    <<

    did you read the part about posting DDL to SQL forums? What you posted makes no sense. In a properly designed table, identifiers are designed on what is called a nominal scale. Have you ever learned about scales and measurements in any of your database classes? This means that your payroll reference number would have to be a string, never an integer. What math are you going to do on it? What quantity or magnitude does it represent? Why are you allowing what seems to be an identifier to ever be null?

    >> How can I *UPDATE* payroll_reference_nbr column in the rows of this resultset with sequential numeric values starting from Max(payroll_reference_nbr) + 1? <<

    Why do you want to mimic a 1950s magnetic tape file in SQL? Your real problem is that your design is fundamentally wrong because you don’t know how RDBMS works. Do you want to fix this or do you want to keep putting one clued on top of another until your schema becomes a total pile of garbage? Wow!  You even use ORDER BY  to mimic a tape file! 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 27, 2019 12:21 AM