---
title: "SQL-005: Update preference row"
summary: Updates an existing USER_PREFERENCE row's text and audit columns.
status: draft
owner: <owner>
last_reviewed: 2026-07-23
library: SAIF.Framework.Preferences
reference_type: package
---

# SQL-005: Update preference row

This UPDATE replaces `PREFERENCE_TEXT` and its audit columns for an existing row, guarded by a no-op check (`PREFERENCE_TEXT <> :prefs`). It backs `PreferenceData.UpdatePref`, invoked from `SavePreferences` when `DoesPrefExist` returns `true`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-005 |
| **Operation type** | Update |
| **Execution method** | Inline SQL (`SqlHelper.ScalarQuery<int>.Execute`) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `PreferenceData` |
| **Criteria property** | n/a (no `IBusinessCriteria`) |
| **Lifecycle method** | `UpdatePref` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:prefs` | Input | string (`SqlHelper.NewParm`) | 4000 | Trimmed preference text; bound once but referenced twice in the statement — in the `SET PREFERENCE_TEXT = :prefs` clause and again in the `WHERE PREFERENCE_TEXT <> :prefs` no-op guard. |
| `:updated` | Input | DateTime (`SqlHelper.NewParm`) | N/A | `System.DateTime.Now` — new audit timestamp for `UPDATE_DATE`. |
| `:updateby` | Input | string (`SqlHelper.NewParm`) | 40 | `DPI.UserID` — new audit updater for `UPDATE_USER`. |
| `:userid` | Input | string (`SqlHelper.NewParm`) | 40 | Lower-cased user ID; row-selection predicate. |
| `:appname` | Input | string (`SqlHelper.NewParm`) | 40 | Application name; row-selection predicate. |
| `:pagename` | Input | string (`SqlHelper.NewParm`) | 40 | Optional page name; only bound when `pageName` is non-empty. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [USER_PREFERENCE](../database-tables.md#user_preference) | UPDATE | `PREFERENCE_TEXT`, `UPDATE_DATE`, `UPDATE_USER`, `USER_ID`, `APP_NAME`, `PAGE_NAME` |

## Sample SQL

```sql
-- Default form (no page name supplied)
Update USER_PREFERENCE
set PREFERENCE_TEXT = :prefs,
    UPDATE_DATE     = :updated,
    UPDATE_USER     = :updateby
where USER_ID  = :userid
  and APP_NAME = :appname
  and PREFERENCE_TEXT <> :prefs
  and PAGE_NAME is NULL
```

```sql
-- When pageName is supplied, the final predicate is replaced with:
Update USER_PREFERENCE
set PREFERENCE_TEXT = :prefs,
    UPDATE_DATE     = :updated,
    UPDATE_USER     = :updateby
where USER_ID  = :userid
  and APP_NAME = :appname
  and PREFERENCE_TEXT <> :prefs
  and PAGE_NAME = :pagename
```

## Operations and processes
- [Update preference row](../operations-processes.md#update-preference-row)
- [Save (upsert/delete) preference](../operations-processes.md#save-upsertdelete-preference)

## Business rules
- [BR-001: User IDs are normalized to lower case](../business-rules.md#br-001-user-ids-are-normalized-to-lower-case)
- [BR-003: Preference identity is scoped by app, user, and optional page](../business-rules.md#br-003-preference-identity-is-scoped-by-app-user-and-optional-page)
- [BR-004: Update is skipped when stored value is unchanged](../business-rules.md#br-004-update-is-skipped-when-stored-value-is-unchanged)
- [BR-005: Preference text is trimmed before storage](../business-rules.md#br-005-preference-text-is-trimmed-before-storage)

## Sources
- SAIF.Framework.Preferences\PreferenceData.cs