SQL help needed..thanks in advance.?

Question

accept p_oldSerial prompt 'Enter Old Serial Number:'
accept p_newSerial prompt 'Enter New Serial Number:'
UPDATE CAR
SET SERIAL = '$p_newSerial'
WHERE SERIAL = '$p_oldSerial';

everytime i run this, i keep getting the '0 rows updated' statement. Could this be because the primary key serial in this table is a foreign key in other tables..?? how should i fix this so that the serial is changed...please help.

Answer 1

Changing primary keys in a table can be problematic, especially if it's used as a foreign key elsewhere. Depending on how referential integrity is set up, it may be preventing the change while other references exist. What you may have to do is a stepwise copy process:



1) Insert a new row into your primary table with the new serial number and the rest of the fields copied from the existing row.

2) Update the foreign key references in any tables they exist to the new serial number

3) Delete the old primary table row



If this sort of thing happens a lot, might want to define a stored procedure or trigger to handle it. Better still, consider using an autonumber as the actual key that is used as foreign key in all the other tables and serial number as an alternate unique key - then you should be free to change it at will since no foreign key references exist (the downside is that if you seach the other tables by serial number a lot, the queries will be more complex since you'll always have to join back to the primary table.)

Answer 2

Assuming no errors (like there's no field named car), there are no records in which the old serial number is the one being entered.

Answer 3

Set ON UPDATE CASCADE for the foreign key, and check what the query returns:



SELECT *

FROM CAR

WHERE SERIAL = '$p_oldSerial';

Answer 4

go with different TotalAmt, class From tableName order by using class, totalAmt different -> provides you with unique mixtures order by using -> type the outcomes first by using class and then by using the complete quantity. sturdy success!