UPDATE SQL

[Serenades]

Hi!

I've got a table containing multiple rows with the exact same information and i want to update ONE column in ONE row with new information but when i use update it updates all rows containing same info... how can i specify which row to update?

Thanx
-Robert-

 

[BasSchouten]

You'll have to find a way to specify the exact row you want to change, by the contents of the fields and then using

UPDATE ... WHERE field1='value1' and field2='value2'

Bas Schouten
System Development & Webdesign
CBIS BV Holland

 

[Serenades]

Well it wont work...

All columns are the same...

I need somehow to lock one row or in someway get it to understand that i want to update ONE row.

-Robert-

 

[courtoisf]

Hi Robert;

The problem sounds like its in the database design, if you actually do have a table with a bunch of rows that are EXACTLY the same in every column. You should never end up with that happening. There has to be some unique way to find a row, which is where Bas is coming from.

 

[DrHeadgear]

I agree, this sounds like quite a strange DB design to me, why are all the rows the same?

Question: how do you know which row to update, assuming there is a specific row you are interested in?

If you want to update only one row, but ANY row, then try using TOP 1 in your statement. My SQL is a little rusty, you might need to use the TOP 1 in a select statement, and then update that line, cant remember if you can use it directly in an update statement.

"Lets integrate!"
If it doesn't work it's not my fault.

 

[Serenades]

Hmm, thats the problem, how do i specify which row to update OR how do i make it to only update ONE row??

TOP 1 ??? Never seen...

-Robert-

 

[courtoisf]

The problem is that two rows that are the same even exist. What logical path are you following to decide whether it is the first record or the second that gets changed if they are exactly the same? And why would you NOT change the other? If there is a logical reason why only one should be changed, number them in your database using an autocounter and always change number 1. FYI: You should refer that other Post you have over to here, (and any others), for better responses.

 

[Serenades]

Well, the database were constructed like that some years ago and its all to late to change that...

Its a movie-register, and every row matches one copy.

So if one copy got lost i have to change ONE of the rows to say that the copy was lost... for example...

We currently use and Omnidex database which are running on a HP3000 server, this works perfectly on HP3000 but when used in VB some of the functions are missing, UPDATE!

Thats the problem, i have to choose some other way of doing it.

-Robert-

 

[BasSchouten]

Well, if you are looking for code to delete just one row of the many that are the same ...

DELETE TOP 1 FROM <table> WHERE <field1> = <value1>

Guess if the DB-design would have correct, every tape should have been identifiable with a unique number, recorded as a field in the table, which you could use in this situation to identify the corresponding record... Bas Schouten
System Development & Webdesign
CBIS BV Holland

http://www.cbis.nl