[MDEV-30737] REPLACE...RETURNING should provide access to the old row data Created: 2023-02-27  Updated: 2023-04-17

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Fix Version/s: None

Type: Task Priority: Major
Reporter: Ashford Nichols Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: beginner-friendly

Issue Links:
Relates
relates to MDEV-5092 Implement UPDATE with result set In Review

 Description   

Given the schema:

CREATE TABLE test (
 
  id INT PRIMARY KEY,
 
  ordinal VARCHAR(10)
 
);
 
INSERT INTO test (id, ordinal) VALUES (1, "first");
 
INSERT INTO test (id, ordinal) VALUES (2, "second");

The following statement returns the data of the newly inserted row (i.e. "third"):

REPLACE INTO test (id, ordinal) VALUES(1, "third") RETURNING ordinal;

There should be some way of accessing the old data that was deleted, perhaps using the 'OLD.<col_name>' syntax, as follows:

REPLACE INTO test (id, ordinal) VALUES(1, "third") RETURNING OLD.ordinal;



 Comments   
Comment by Sergei Golubchik [ 2023-02-28 ]

Unfortunately OLD. syntax doesn't work here, REPLACE can delete multiple rows:

create table t1 (a int, unique (a), b int, unique (b), c int);
 
insert t1 values (1,0,1), (2,3,2);
 
replace t1 values (1,3,10);

It would be a good feature to be able to return deleted rows in REPLACE. But it's not clear what syntax can be used for it.

Generated at Thu Feb 08 10:18:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.