How to do this update?

[al]

Greetings,

In the customers table in Northwind db, one can update PK
(customerid) and all other fields in the same table. My question is
how can you do this in the udpate stat. That is, if one wants to
write update query to update all fields including PK, how it can be
set? Using PK in the SET statement, gives an error, because this field
might have changed during the update?

MTIA,
Grawsha

[Erland Sommarskog]

al (gr*********@yahoo.com) writes:

In the customers table in Northwind db, one can update PK
(customerid) and all other fields in the same table. My question is
how can you do this in the udpate stat. That is, if one wants to
write update query to update all fields including PK, how it can be
set? Using PK in the SET statement, gives an error, because this field
might have changed during the update?

Updating the PK does not have to be a problem:

CREATE TABLE x (a int NOT NULL PRIMARY KEY,
b varchar(23) NOT NULL)
go
INSERT x VALUES( 1, 'KJK')
INSERT x VALUES( 2, 'NJJDGF')
go
UPDATE x
SET a = 10,
b = 'Hallå!'
WHERE a = 1
go
SELECT * FROM x

However, this fails:

UPDATE Northwind..Customers
SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'

And the error message tells us why:

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint
'FK_Orders_Customers'. The conflict occurred in database 'Northwind',
table 'Orders', column 'CustomerID'.
The statement has been terminated.

Since there is a reference to the table, you cannot change the id
of a customer that has orders. If you added a new customer to the table,
you could easily change its ID, as you long as you don't add orders for
it.

One way to handle this, is to change the foreign-key defintion to say
ON UPDATE CASCADE, in which case the change would be propagated to
Orders.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[al]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

al (gr*********@yahoo.com) writes:

In the customers table in Northwind db, one can update PK
(customerid) and all other fields in the same table. My question is
how can you do this in the udpate stat. That is, if one wants to
write update query to update all fields including PK, how it can be
set? Using PK in the SET statement, gives an error, because this field
might have changed during the update?
Updating the PK does not have to be a problem:

CREATE TABLE x (a int NOT NULL PRIMARY KEY,
b varchar(23) NOT NULL)
go
INSERT x VALUES( 1, 'KJK')
INSERT x VALUES( 2, 'NJJDGF')
go
UPDATE x
SET a = 10,
b = 'Hallå!'
WHERE a = 1
go
SELECT * FROM x

However, this fails:

UPDATE Northwind..Customers
SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'

And the error message tells us why:

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint
'FK_Orders_Customers'. The conflict occurred in database 'Northwind',
table 'Orders', column 'CustomerID'.
The statement has been terminated.

Since there is a reference to the table, you cannot change the id
of a customer that has orders. If you added a new customer to the table,
you could easily change its ID, as you long as you don't add orders for
it.

One way to handle this, is to change the foreign-key defintion to say
ON UPDATE CASCADE, in which case the change would be propagated to
Orders.

I don't have a problem with this. I did ticked the CascadeOnUpdate.
The problem is, the update woun't happen becuase there will be a
Concurency Violation. Try to do this(with all cascades, and still you
will recieve an err)
UPDATE Northwind..Customers SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'

[David Portas]

> UPDATE Northwind..Customers

SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'

This UPDATE works for me once I've enabled Cascading updates on the child
tables (CustomerCustomerDemo and Orders). Exactly what error message are you
getting? Maybe you already have a row where CustomerID = 'KKKKK' so this
violates the primary key?

--
David Portas
------------
Please reply only to the newsgroup
--

[Erland Sommarskog]

al (gr*********@yahoo.com) writes:

I don't have a problem with this. I did ticked the CascadeOnUpdate.
The problem is, the update woun't happen becuase there will be a
Concurency Violation. Try to do this(with all cascades, and still you
will recieve an err)
UPDATE Northwind..Customers
SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'

Since I am lazy I did not even try this. I know that it does not produce
any error with with the appropriate cascade. Least of all concurrency
violation, because SQL Server does not produce any such errors.

However, some client tools and libraries are doing smart things behind
your back, and may be outsmarted by your manoevre.

So you need to tell us in which context you get the error message (as
well as of course the exact text of the error message). I have a strong
feeling that you are not submitting the above from Query Analyzer.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[al]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

al (gr*********@yahoo.com) writes:

I don't have a problem with this. I did ticked the CascadeOnUpdate.
The problem is, the update woun't happen becuase there will be a
Concurency Violation. Try to do this(with all cascades, and still you
will recieve an err)
UPDATE Northwind..Customers
SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'

Since I am lazy I did not even try this. I know that it does not produce
any error with with the appropriate cascade. Least of all concurrency
violation, because SQL Server does not produce any such errors.

However, some client tools and libraries are doing smart things behind
your back, and may be outsmarted by your manoevre.

So you need to tell us in which context you get the error message (as
well as of course the exact text of the error message). I have a strong
feeling that you are not submitting the above from Query Analyzer.

You are right! I'm doing this from VB.NET. But since this is not the
group for such post and since I have found out about this late, how
can I fix this? I gusse I need to submit the original value+the
changed value..may be??

[Erland Sommarskog]

al (gr*********@yahoo.com) writes:

You are right! I'm doing this from VB.NET. But since this is not the
group for such post and since I have found out about this late, how
can I fix this? I gusse I need to submit the original value+the
changed value..may be??

I'm still a learner of ADO .Net, so maybe I am not the one to give
expert advice. But even as an expert, I would have problems without
your code at hand.

The answer to your question may be in David Sceppa's book on ADO .Net
which lies next to me on the table. I don't find anything on a quick
look, though. But it's a good book.

Being an SQL person, I would probably define my own UpdateCommand
for the DataAdapter, but there may be better support build into
ADO .Net. If you find some ADO .Net group, you might get better
answers there.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp