17 lines
615 B
MySQL
17 lines
615 B
MySQL
|
ALTER TABLE hardware_assets
|
||
|
|
ADD COLUMN IF NOT EXISTS location_display_order INTEGER;
|
||
|
|
|
||
|
|
WITH ordered AS (
|
||
|
|
SELECT id, ROW_NUMBER() OVER (PARTITION BY current_location_id ORDER BY brand, model, serial_number, id) AS row_number
|
||
|
|
FROM hardware_assets
|
||
|
|
WHERE current_location_id IS NOT NULL AND location_display_order IS NULL
|
||
|
|
)
|
||
|
|
UPDATE hardware_assets h
|
||
|
|
SET location_display_order = ordered.row_number
|
||
|
|
FROM ordered
|
||
|
|
WHERE h.id = ordered.id;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_hardware_assets_location_display_order
|
||
|
|
ON hardware_assets(current_location_id, location_display_order)
|
||
|
|
WHERE deleted_at IS NULL;
|