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.
| Author |
Topic |
|
krisrajz
Starting Member
19 Posts |
 Posted - 2007-02-04 : 04:46:13
|
| How can I update/delete record(s) as specific location without using Primary Key or where using Primary Key is not possible? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-04 : 08:02:50
|
You would need to use a WHERE clause that uniquely identifies the record.UPDATE USET MyColumn = 'SomeValue'FROM MyTable AS U -- Optional join JOIN MyOtherTable AS X ON X.SomeColumn = U.SomeColumnWHERE U.UniqueColumn1 = 'SomeValue1' AND U.UniqueColumn2 = 'SomeValue2' Kristen |
 |
|
|
krisrajz
Starting Member
19 Posts |
Posted - 2007-02-05 : 02:41:52
|
| If rows are not unique? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-05 : 02:45:51
|
| "If rows are not unique?"Well, I would assume that the rows in a table are different, otherwise you will have an exact duplicate.Normally a combination of several columns will be unique.Failing that you can include EVERY column in the WHERE clause, so that you restrict to the original record (making sure to handle NULL properly).However, IF there are two rows which are identical [in every column] then then will BOTH get updated.One option might be to add an IDENTITY column, which will provide a unique value for each row, and then you can use that to control the Update.Kristen |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-05 : 02:46:36
|
quote:
Originally posted by krisrajz
If rows are not unique?
Then how do you identify which one do you want to delete if it is not unique ?
KH |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-05 : 02:50:41
|
| Take a look at this:[url]http://support.microsoft.com/kb/139444[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
|
|
|
Positioned Updates/Deleted without PK