Update primary key in two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update primary key in two tables

Author

Topic

sjmorgan
Starting Member

9 Posts

Posted - 2003-05-08 : 08:51:00

I need to update the primary key field in two tables sharing this field.

I can't update separately because I get an integrity violation message that tells me the field is still being referenced from the other table, fair enough but is there any way of doing a joined/linked/double update to the primary key of both tables simultaneously?

Thanks!

Stuart

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-08 : 09:18:22

No and update only effects one table.

You should INSERT rather than UPDATE ...

So if you have orders and order_detail and you want to change the order_no ...

<psuedo>
insert orders
select new_order_no, otherstuff
from orders
where order_no = old_order_no

update orderdetail
set order_no = new_order_no
where order_no = old_order_no

delete orders
where order_no = old_order_no
</psuedo>

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-08 : 10:38:23

Probably cause a disagreement but my opinion is that you should never update a primary key.
Delete and insert another row.
Changing a primary key means that you are deleting that entity and adding another as the primary key identifies the entity - otherwise the primary key is incorrect.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-09 : 17:47:58

I know it can be dangerous to use, but isn't this why SQL has cascading updates as an option ??

just set the FK relationship to use cascading updates and you are good to go.

I do agree with Nigel, however ...

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-09 : 19:06:43

quote:
just set the FK relationship to use cascading updates and you are good to go.

Assuming he's using SQL Server 2000. If he's not...

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-09 : 19:41:37

quote:

quote:
just set the FK relationship to use cascading updates and you are good to go.
Assuming he's using SQL Server 2000. If he's not...

Then remove the constraints and get to writing triggers. Or upgrade already!

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

sjmorgan
Starting Member

9 Posts

Posted - 2003-05-12 : 03:59:49

I am using SQL 2k so I will investigate the cascading updates option - thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources