Insert and Delete
in [Using MS SQL Server]
|
From: Plamen Ratchev on 16 Apr 2010 12:29 How is that different than the UPDATE statement I posted? When you delete and insert you in essence simulate an update. -- Plamen Ratchev http://www.SQLStudio.com
From: Gert-Jan Strik on 17 Apr 2010 17:10 There is no rule that says that you cannot update the Primary Key. Have you tried Plamen's query? What was the error message? -- Gert-Jan Faye wrote: > > salesman is the primary key. It can not be updated. > > On Apr 15, 5:00 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > > Not sure what you need, but try this (make sure to try on test data not directly on production): > > > > UPDATE Tables > > SET salesman = 30 > > WHERE salesman = 25 > > AND customer IN ('Jeff', 'Kory', 'Jean'); > > > > If you need to update all customers, then this: > > > > UPDATE Tables > > SET salesman = 30 > > WHERE salesman = 25; > > > > -- > > Plamen Ratchevhttp://www.SQLStudio.com
From: Erland Sommarskog on 18 Apr 2010 17:37 Plamen Ratchev (Plamen(a)SQLStudio.com) writes: > How is that different than the UPDATE statement I posted? When you delete > and insert you in essence simulate an update. There could be a trigger that screams blue murder if he touches the PK in an UPDATE statement. Or if he updates the PK for more than one row at a time. Or it could be as simple as that there is a referencing table with a FK constraint that does not have ON UPDATE CASCADE. In thesse case, Faye is indeed better off with an INSERT and a DELETE. And in the case of the referencing FKs, he needs to update these, before he deletes. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Faye on 20 Apr 2010 17:00
This is exactly my situation. On Apr 18, 4:37 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Plamen Ratchev (Pla...(a)SQLStudio.com) writes: > > How is that different than the UPDATE statement I posted? When you delete > > and insert you in essence simulate an update. > > There could be a trigger that screams blue murder if he touches the PK > in an UPDATE statement. Or if he updates the PK for more than one row at a > time. Or it could be as simple as that there is a referencing table with a > FK constraint that does not have ON UPDATE CASCADE. > > In thesse case, Faye is indeed better off with an INSERT and a DELETE. And > in the case of the referencing FKs, he needs to update these, before he > deletes. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |