Sql mutations: Difference between revisions
From wikinotes
(Created page with "= Insertion = <blockquote> You create new rows using the <code>INSERT</code> statement. <syntaxhighlight lang="MySQL"> #### Basic Insert ## table ( columns...") |
|||
| Line 1: | Line 1: | ||
= | = INSERT = | ||
<blockquote> | <blockquote> | ||
Add rows to a table. | |||
Insert | |||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
# | # table ( columns ) | ||
# | | | |||
# | INSERT INTO tasks(subject, start_date, end_date, description) | ||
INSERT INTO tasks(subject,start_date,end_date,description) | |||
VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'), | VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'), | ||
('Task 2','2010-01-01','2010-01-02','Description 2'), | ('Task 2','2010-01-01','2010-01-02','Description 2'), | ||
('Task 3','2010-01-01','2010-01-02','Description 3'); | ('Task 3','2010-01-01','2010-01-02','Description 3'); | ||
</syntaxhighlight> | |||
Insert where not exist. | |||
# | <syntaxhighlight lang="MySQL"> | ||
INSERT INTO | # Add 'Rupert' to users if does not exist | ||
INSERT INTO users (name, address, tele) | |||
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp | SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp | ||
WHERE NOT EXISTS ( | WHERE NOT EXISTS (SELECT name FROM users WHERE name = 'Rupert') LIMIT 1; | ||
</syntaxhighlight> | |||
) LIMIT 1; | |||
Update where column already exists | |||
<syntaxhighlight lang="MySQL"> | |||
UPDATE users | |||
set email = "user@domain.com" | |||
WHERE id = 190; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Insertion --> | </blockquote><!-- Insertion --> | ||
Revision as of 17:51, 19 September 2021
INSERT
Add rows to a table.
Insert
# table ( columns ) # | | INSERT INTO tasks(subject, start_date, end_date, description) VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'), ('Task 2','2010-01-01','2010-01-02','Description 2'), ('Task 3','2010-01-01','2010-01-02','Description 3');Insert where not exist.
# Add 'Rupert' to users if does not exist INSERT INTO users (name, address, tele) SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp WHERE NOT EXISTS (SELECT name FROM users WHERE name = 'Rupert') LIMIT 1;Update where column already exists
UPDATE users set email = "user@domain.com" WHERE id = 190;
Deletion
DELETE FROM userDepartmentTable ##Deleting Rows works just like adding them WHERE project_Id = 43 AND user_Id = 190;
