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...")
 
m (Will moved page Sql: mutations to Sql mutations without leaving a redirect)
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
= Insertion =
Changing data in your database.<br>
See [[sql schemas]] for instructions on how to change your database structure.
 
= INSERT =
<blockquote>
<blockquote>
You create new rows using the <code>INSERT</code> statement.
Add rows to a table.


Insert multiple rows.
<syntaxhighlight lang="MySQL">
<syntaxhighlight lang="MySQL">
#### Basic Insert
#           table (          columns                       )
##          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 row if match does not exist.
#### Insert Where Not Exist
<syntaxhighlight lang="MySQL">
INSERT INTO table_listnames (name, address, tele)
# 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;
    SELECT name FROM table_listnames WHERE name = 'Rupert'
</syntaxhighlight>
) LIMIT 1;
</blockquote><!-- Insertion -->


= UPDATE =
<blockquote>
Update an existing row.


#### Update where column already exists
<syntaxhighlight lang="MySQL">
UPDATE userTable
UPDATE users
set    email = 'will@mfw'
set    email = "user@domain.com"
WHERE  user_Id = 190;
WHERE  id = 190;
</syntaxhighlight>


</syntaxhighlight>
</blockquote><!-- UPDATe -->
</blockquote><!-- Insertion -->


= Deletion =
= DELETE =
<blockquote>
<blockquote>
Delete an existing row.
<syntaxhighlight lang="MySQL">
<syntaxhighlight lang="MySQL">
DELETE FROM userDepartmentTable          ##Deleting Rows works just like adding them
DELETE FROM users
WHERE project_Id = 43
WHERE id = 100;
AND  user_Id    = 190;
 
</syntaxhighlight>
</syntaxhighlight>
</blockquote><!-- Deletion -->
</blockquote><!-- DELETE -->

Latest revision as of 18:37, 19 September 2021

Changing data in your database.
See sql schemas for instructions on how to change your database structure.

INSERT

Add rows to a table.

Insert multiple rows. 

#           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 row if match does 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

Update an existing row. 

UPDATE users
set    email = "user@domain.com"
WHERE  id = 190;

DELETE

Delete an existing row. 

DELETE FROM users
WHERE id = 100;
Retrieved from "http://willpittman.net:8080/index.php?title=Sql_mutations&oldid=27612"