The syntax is as follows:
Insert value1 into Column1, value2 into Column2, and value3 into Column3:
INSERT INTO TableName (Column1, Column2, Column3) VALUES (value1, value2, value3)
Insert one record (values are inserted in the order that the columns appear in the database):
INSERT INTO TableName VALUES (value1, value2, value3)
Insert two records:
INSERT INTO TableName VALUES (value1, value2, value3), (value4, value5, value6) INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00); INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');
You can also insert records 'selected' from other table.
INSERT INTO table1(field1, field2) SELECT field1, field2 FROM table2 INSERT INTO World_Events SELECT * FROM National_Events
The syntax is:
UPDATE table SET field1 = newvalue1, field2 = newvalue2 WHERE criteria ORDER BY field LIMIT n
UPDATE owner SET ownerfirstname = 'John' WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase'); UPDATE antiques SET price = 500.00 WHERE item = 'Chair'; UPDATE order SET discount=discount * 1.05 UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID SET tbl1.col1 = tbl1.col1 + 1 WHERE tbl2.status='Active' UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz') UPDATE products_categories AS pc INNER JOIN products AS p ON pc.prod_id = p.id SET pc.prod_sequential_id = p.sequential_id UPDATE table_name SET col_name = REPLACE(col_name, 'host.domain.com', 'host2.domain.com') UPDATE posts SET deleted=True ORDER BY date LIMIT 1
With ORDER BY you can order the rows before updating them, and only update a given number of rows (LIMIT).
It is currently not possible to update a table while performing a subquery on the same table. For example, if I want to reset a password I forgot in SPIP:
mysql> UPDATE spip_auteurs SET pass = (SELECT pass FROM spip_auteurs WHERE login='paul') where login='admin'; ERROR 1093 (HY000): You can't specify target table 'spip_auteurs' for update in FROM clause
TODO: describes a work-around that I couldn't make to work with MySQL 4.1. Currently the work-around is not use 2 subqueries, possibly with transactions.
REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.
Since MySQL 5.5, "INSERT IGNORE" and "REPLACE IGNORE" allow, when a duplicate key error occurs, to display some warnings and avoid the statement to abort.
Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations.
DELETE [QUICK] FROM `table1` TRUNCATE [TABLE] `table1`
DELETE FROM `antiques` WHERE item = 'Ottoman' ORDER BY `id` LIMIT 1
You can order the rows before deleting them, and then delete only a given number of rows.
Manage research, learning and skills at NCR Works. Create an account using LinkedIn to manage and organize your omni-channel knowledge. NCR Works is like a shopping cart for information -- helping you to save, discuss and share.