How is data modified in a relational database?

Speaking from a SQLServer perspective using an UPDATE operation you can modify the data. Internally, it uses two temp table INSERTED and DELETED for the purpose. DELETED will have old data and INSERTED the new data. Based on whether UPDATE operation was success, it applies either the content of INSERTED or the DELETED table to the main table to make the table display modified data in case of successful UPDATE operation or the existing old data itself in case the UPDATE ended in a failure (due to constraint violation, transaction failure etc)