Insert and Update multiple records at once

INSERT Statement
If you want to insert a numbers of records, try the following:

INSERT INTO TABLE_NAME1 ('COLUMN_A', 'COLUMN_B')
VALUES
(VALUE_A11, VALUE_B12),
(VALUE_A21, VALUE_B22);

For MySQL syntax, please visit MySQL :: MySQL 5.1 Reference Manual :: 13.2.5 INSERT Syntax
For PostgreSQL syntax, please visit PostgreSQL: Documentation: 9.3: Inserting Data
UPDATE Statement

UPDATE TABLE_NAME1 AS T1
SET(A, B) = (
SELECT VALUE1, VALUE2 FROM TABLE_NAME2 AS T2
WHERE T1.COL1=T2.COL2
)

There’s a problem with above UPDATE statement. In the subquery “SELECT VALUE1, VALUE2 FROM TABLE_NAME2 AS T2 WHERE T1.COL1=T2.COL2”, if it returns NULL values, your query could update with those NULL values. Hence, you should have a WHERE condition to prevent this problem, for example:

UPDATE TABLE_NAME1 AS T1
SET(A, B) = (
SELECT VALUE1, VALUE2 FROM TABLE_NAME2 AS T2
WHERE T1.COL1=T2.COL2
)
WHERE A > 5

For MySQL syntax, please visit MySQL :: MySQL 5.1 Reference Manual :: 13.2.10 UPDATE Syntax
For PostgreSQL syntax, please visit PostgreSQL: Documentation: 9.3: UPDATE

Advertisements