want to change identity column so numbers begin with 7...

UPDATE orders SET orderid = 7000000 WHERE orderid = 1287015

Cannot update identity column 'orderid'.
LVL 1
rgb192Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
please mention all you column names,  your table has more than 20 columns and you just mentioned 3 of those "OrderID, ordertype, company" here , you MUST mention  all the colunnnames
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you cannot update the indentity column
all you can do is
enable the IDENTITY_INSERT , duplicate that row with the OrderID 7000000... and delete the old one

SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, Col2, col3 , col4 )  ------------ You MUST mention all the columns here
SELECT 7000000, Col2,Col3,Col4 FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

DELETE FROM Orders WHERE orderid = 1287015

You need to check the current identity value after this
0
 
rgb192Author Commented:
>>You MUST mention all the columns here
should  I do all columns
which columns should i leave out
what about keys??




CREATE TABLE [dbo].[orders](
      [orderid] [int] IDENTITY(1,1) NOT NULL,
      [ordertype] [varchar](1) NOT NULL,
      [company] [varchar](50) NOT NULL,
      [statusid] [int] NOT NULL,
      [shippable] [int] NULL CONSTRAINT [DF_orders_shipppable]  DEFAULT ((1)),
      [dateordered] [datetime] NOT NULL,
      [datepaid] [datetime] NOT NULL,
      [dateneeded] [datetime] NOT NULL,
      [customernote] [text] NOT NULL,
      [BillFirstName] [varchar](100) NOT NULL,
      [BillLastName] [varchar](100) NOT NULL,
      [BillCompany] [varchar](100) NOT NULL,
      [BillAddress1] [varchar](100) NOT NULL,
      [BillAddress2] [varchar](100) NOT NULL,
      [BillCity] [varchar](100) NOT NULL,
      [BillState] [varchar](100) NOT NULL,
      [BillZip] [varchar](15) NOT NULL,
      [BillCountry] [varchar](100) NOT NULL,
      [BillPhone1] [varchar](50) NOT NULL,
      [BillPhone2] [varchar](50) NOT NULL,
      [ShipFirstName] [varchar](100) NOT NULL,
      [ShipLastName] [varchar](100) NOT NULL,
      [ShipCompany] [varchar](100) NOT NULL,
      [ShipAddress1] [varchar](100) NOT NULL,
      [ShipAddress2] [varchar](100) NOT NULL,
      [ShipCity] [varchar](100) NOT NULL,
      [ShipState] [varchar](100) NOT NULL,
      [ShipZip] [varchar](15) NOT NULL,
      [ShipCountry] [varchar](100) NOT NULL,
      [ShipPhone1] [varchar](50) NOT NULL,
      [ShipPhone2] [varchar](50) NOT NULL,
      [ShipVia] [varchar](100) NOT NULL,
      [email] [varchar](250) NOT NULL,
      [email2] [varchar](100) NOT NULL,
      [salesman] [int] NOT NULL,
      [tax] [money] NOT NULL,
      [insurance] [money] NOT NULL CONSTRAINT [DF_orders_insurance]  DEFAULT ((0)),
      [shippingcharge] [money] NOT NULL,
      [itemid] [bigint] NULL,
      [transactionid] [bigint] NULL,
      [shiptosame] [varchar](5) NULL,
      [packageid] [int] NULL CONSTRAINT [DF_orders_boxtype]  DEFAULT ((0)),
      [ntbjemail] [int] NOT NULL CONSTRAINT [DF_orders_ntbjemail]  DEFAULT ((0)),
      [apemail] [int] NOT NULL CONSTRAINT [DF_orders_apemail]  DEFAULT ((0)),
      [contactstatus] [int] NOT NULL CONSTRAINT [DF_orders_contactstatus]  DEFAULT ((1)),
 CONSTRAINT [PK__orders__0BC6C43E] PRIMARY KEY CLUSTERED
(
      [orderid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company,  )  ------------ fill the rest, just columnNames
SELECT 7000000, Col2,Col3,Col4  ------------ fill the rest, just columnNames
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

DELETE FROM Orders WHERE orderid = 1287015

You need to check the current identity value after this
0
 
rgb192Author Commented:
do I do this with constraint values

   [packageid] [int] NULL CONSTRAINT [DF_orders_boxtype]  DEFAULT ((0)),
      [ntbjemail] [int] NOT NULL CONSTRAINT [DF_orders_ntbjemail]  DEFAULT ((0)),
      [apemail] [int] NOT NULL CONSTRAINT [DF_orders_apemail]  DEFAULT ((0)),
      [contactstatus] [int] NOT NULL CONSTRAINT [DF_orders_contactstatus]  DEFAULT ((1)),



all column names????
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
just the column names as i shown in my example, you dont have to include the datatype nor the constraint
0
 
rgb192Author Commented:
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company)  
SELECT 7000000, OrderID, ordertype, company  
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF


Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company)  
SELECT 7000000,  ordertype, company  ---------------------------- you dont have to specify the orderId again
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

0
 
rgb192Author Commented:
SET IDENTITY_INSERT orders ON
INSERT INTO Orders (OrderID, ordertype, company)  
SELECT 7000000,  ordertype, company
FROM orders WHERE orderid = 1287015
SET IDENTITY_INSERT orders OFF

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'statusid', table 'database.dbo.orders'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
 
rgb192Author Commented:

works

will next order be
7000001
0
 
rgb192Author Commented:
works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.