In-depth study on the use of table alias for update and delete in database

Summarize

Update

SQL statement example	Oracle	SQLite	PostgreSQL	MYSQL & mariadb
`UPDATE users as a SET = 111 WHERE = 'Alice';`	Report an error	Report an error	Report an error	Normal execution
`UPDATE users as a SET = 111 WHERE name = 'Alice';`	Report an error	Report an error	Report an error	Normal execution
`UPDATE users as a SET age = 111 WHERE = 'Alice';`	Report an error	Normal execution	Normal execution	Normal execution
`UPDATE users as a SET age = 111 WHERE name = 'Alice';`	Report an error	Normal execution	Normal execution	Normal execution

Delete

SQL statement example	Oracle	SQLite	PostgreSQL	MYSQL & mariadb
`delete from users as a WHERE = 'Alice';`	Report an error	Report an error	Report an error	Report an error
`delete from users as a WHERE name = 'Alice';`	Report an error	Report an error	Report an error	Report an error

1 Update

1.1 Test cases UPDATE users as a SET = 111 WHERE = 'Alice';

UPDATE users as a SET = 111 WHERE = 'Alice';

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age INT
);

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);

SELECT * FROM users;
UPDATE users as a SET  = 111 WHERE  = 'Alice';
SELECT * FROM users;

The contents of the previous table are as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

1 ORACLE

Execution error

ORA-00971: missing SET keyword

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

2 sqlite

Execution error

Error: near line 12: near ".": syntax error

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

3 PG

Execution error

psql::12: ERROR:  column "a" of relation "users" does not exist
LINE 1: UPDATE users as a SET  = 111 WHERE  = 'Alice';

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

4 MYSQL & mariadb

Perform normally

The content of the table has been updated as follows:

name	age
Alice	111
Bob	30
Charlie	35
Alice	111

1.2 Test cases UPDATE users as a SET = 111 WHERE name = 'Alice';

UPDATE users as a SET = 111 WHERE name = 'Alice';

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age INT
);

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);

SELECT * FROM users;
UPDATE users as a SET  = 111 WHERE name = 'Alice';
SELECT * FROM users;

The contents of the previous table are as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

1 ORACLE

Execution error

ORA-00971: missing SET keyword

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

2 sqlite

Execution error

Error: near line 12: near ".": syntax error

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

3 PG

Execution error

psql::12: ERROR:  column "a" of relation "users" does not exist
LINE 1: UPDATE users as a SET  = 111 WHERE name = 'Alice';

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

4 MYSQL & mariadb

Perform normally

The content of the table has been updated as follows:

name	age
Alice	111
Bob	30
Charlie	35
Alice	111

1.3 Test cases UPDATE users as a SET age = 111 WHERE = 'Alice';

UPDATE users as a SET age = 111 WHERE = 'Alice';

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age INT
);

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);

SELECT * FROM users;
UPDATE users as a SET age = 111 WHERE  = 'Alice';
SELECT * FROM users;

The contents of the previous table are as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

1 ORACLE

Execution error

ORA-00971: missing SET keyword

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

2 sqlite

Perform normally

The content of the table has been updated as follows:

name	age
Alice	111
Bob	30
Charlie	35
Alice	111

3 PG

Perform normally

The content of the table has been updated as follows:

name	age
Bob	30
Charlie	35
Alice	111
Alice	111

4 MYSQL & mariadb

Perform normally

The content of the table has been updated as follows:

name	age
Alice	111
Bob	30
Charlie	35
Alice	111

1.4 Test cases UPDATE users as a SET age = 111 WHERE name = 'Alice';

UPDATE users as a SET age = 111 WHERE name = 'Alice';

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age INT
);

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);

SELECT * FROM users;
UPDATE users as a SET age = 111 WHERE name = 'Alice';
SELECT * FROM users;

The contents of the previous table are as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

1 ORACLE

Execution error

ORA-00971: missing SET keyword

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

2 sqlite

Perform normally

The content of the table has been updated as follows:

name	age
Alice	111
Bob	30
Charlie	35
Alice	111

3 PG

Perform normally

The content of the table has been updated as follows:

name	age
Bob	30
Charlie	35
Alice	111
Alice	111

4 MYSQL & mariadb

Perform normally

The content of the table has been updated as follows:

name	age
Alice	111
Bob	30
Charlie	35
Alice	111

2 delete

2.1 Test case delete users as a from a WHERE = 'Alice';

delete users as a from a WHERE = 'Alice';

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age INT
);

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);

SELECT * FROM users;
delete users as a from a WHERE  = 'Alice';
SELECT * FROM users;

The contents of the previous table are as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

1 ORACLE

Execution error

ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete
users '

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

2 sqlite

Execution error

Error: near line 12: near "users": syntax error

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

3 PG

Execution error

psql::12: ERROR:  syntax error at or near "users"
LINE 1: delete users as a from a WHERE  = 'Alice';

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

4 MYSQL & mariadb

Execution error

ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a from a WHERE  = 'Alice'' at line 1

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

2.2 Test case delete users as a from a WHERE name = 'Alice';

delete users as a from a WHERE name = 'Alice';

CREATE TABLE users (
    name VARCHAR(255) NOT NULL,
    age INT
);

INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 35);
INSERT INTO users (name, age) VALUES ('Alice', 40);

SELECT * FROM users;
delete users as a from a WHERE name = 'Alice';
SELECT * FROM users;

The contents of the previous table are as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

1 ORACLE

Execution error

ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete
users '

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

2 sqlite

Execution error

Error: near line 12: near "users": syntax error

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

3 PG

Execution error

psql::12: ERROR:  syntax error at or near "users"
LINE 1: delete users as a from a WHERE name = 'Alice';

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

4 MYSQL & mariadb

Execution error

ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a from a WHERE name = 'Alice'' at line 1

The content of the query table has not changed after that, as follows:

name	age
Alice	25
Bob	30
Charlie	35
Alice	40

Summarize

This is the article about the use of table alias for update and delete in the database. For more related content on update and delete, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!