InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 00:39:30 GMT20http://www.sqlservercentral.com/Forums/Topic1422041-1292-1.aspxTry this, Wayne:[code="sql"]USE Occupancy;WITH BookingsToUpdate AS ( SELECT *, Seq = ROW_NUMBER() OVER (ORDER BY Booking_Skey) FROM Bookings)SELECT Seq, Booking_Skey, BookingNumber = 'B' + RIGHT('0000000' + CAST(Seq AS VARCHAR(8)),8), AnotherBookingNumber = 'B' + RIGHT('0000000' + CAST(Booking_Skey AS VARCHAR(8)),8)FROM BookingsToUpdate[/code]Thu, 21 Feb 2013 03:21:20 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1422041-1292-1.aspxHi DwainFor some reason that didn't work, but I found another solution.USE OccupancyUpdate tSet BookingNumber = 'B' + Right ('00000000' + CAST (Seq AS varchar (30)), 8)FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY Booking_Skey) AS Seq FROM Bookings)tThanks for your help.Thu, 21 Feb 2013 03:04:39 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1422041-1292-1.aspx[quote][b]ChrisM@Work (2/20/2013)[/b][hr][i]Always [/i]run the corresponding SELECT first. It will show you which rows will be updated, and from what to what. The corresponding SELECT for this UPDATE is this:[/quote]+1 INSERTs too!Wed, 20 Feb 2013 17:57:17 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1422041-1292-1.aspx[quote][b]wafw1971 (2/20/2013)[/b][hr]I have a column on my database called Booking Number this is to be used on correspondence etc however I don't want it to be Booking Number to be just a 1, I want it to be B000000001. How can I accomplish this?I have tried but it has not worked.USE OccupancyUpdate BookingsSet BookingNumber = 'B' + Right ('00000000' + CAST (BookingNumber AS varchar (30)), 8)WHERE BookingNumber = '0' ThanksWayne[/quote][i]Always [/i]run the corresponding SELECT first. It will show you which rows will be updated, and from what to what. The corresponding SELECT for this UPDATE is this:[code="sql"]SELECT BookingNumber, NewBookingNumber = 'B' + RIGHT ('00000000' + CAST (BookingNumber AS varchar (30)), 8)FROM BookingsWHERE BookingNumber = '0' [/code]So - which rows will be updated? If the rows displayed don't match your requirement, then change the WHERE clause Is the new value correct? If not, then change the expression. You're casting BookingNumber to VARCHAR(30); is it really a 30 digit number? Will the rightmost 8 digits of a 30-digit number be meaningful?Wed, 20 Feb 2013 06:18:27 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1422041-1292-1.aspxIs booking number varchar(30) in the DB?If not look at computed columns and stick an extra column on the end of the table with the formula given to convert it to B00000000 etcWed, 20 Feb 2013 05:59:30 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1422041-1292-1.aspxI have a column on my database called Booking Number this is to be used on correspondence etc however I don't want it to be Booking Number to be just a 1, I want it to be B000000001. How can I accomplish this?I have tried but it has not worked.USE OccupancyUpdate BookingsSet BookingNumber = 'B' + Right ('00000000' + CAST (BookingNumber AS varchar (30)), 8)WHERE BookingNumber = '0' ThanksWayneWed, 20 Feb 2013 05:56:18 GMTwafw1971