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?
[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.
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.