Replacing a record.

I have a table with several columns.
The first column is the primary key and must be unique.
Two other columns must also be unique. Lets call them column A, B and C. (There are other columns but they are not relevant.)

When I want to update this record (Say I wanted to change the text of the primary key.)
How do I update the record in the database?

UPDATE `MyTable` SET `A`="NewValue", `B`="SameValueAsBefore", `C`="AlsoSameValueAsBefore" WHERE `A`="OldValue";

Is this the proper way to update when you want to update the primary key? Or must you delete the record and add a new one?

Why not use a RecordSet?

Dim rs As RecordSet = MyDB.SQLSelect("Select A from MyTable where A=OldValue") rs.Edit rs.Field("A").Value = NewValue rs.Update

Something like that. :slight_smile:

I personally prefer the “SQL” method…

just make sure you add error checking especailly if you a messing with the PK. as it MUST remain UNIQUE to the table.

and you can say

UPDATE MyTable SET A=“NewValue” WHERE A=“OldValue”;

no need to worry what the other values are… you aren’t messing with them

I had no idea about the rs.edit/rs.update methods… cool! but does that work with all databases?

I included the other fields in the sql statement because I wanted to be sure that if they had changed that their uniqueness would be tested.

[quote=21156:@Brian O’Brien]I had no idea about the rs.edit/rs.update methods… cool! but does that work with all databases?

I included the other fields in the sql statement because I wanted to be sure that if they had changed that their uniqueness would be tested.[/quote]
That works with all supported databases, yes. :slight_smile:

As Dave said, you can also do with pure SQL. Depending on your comfort with SQL, you may choose to go this route. If I was changing a mass of records, that’s certainly how I’d do it.

If you retrieve a set of records, and want iterate on them and change specifically the current record by direct SQL, you should not rely on the current changeable data like A,B,C in the WHERE clause. You should use the ID (primary key) of the record. So, pretending your DB has ID, A,B,C data; and after some recordset.movenext() you stopped at current FIELD(“ID”) = 10, and you used A,B,C for anything, you can change the current values like:

UPDATE MyTable SET A=“N1”, B=“N2”, C=“N3” WHERE ID=10;

If you do it that way, you’ll have to worry about converting those N1 N2 string to into a SQL-friendly version which can be surprisingly difficult. Using rs.edit lifts that burden from your shoulders, as XOJO does the conversion for you.
You might also check into using prepared statements for that purpose.