# 9. Modifying Data

### Insert

**(*****guide you on how to insert a single row into a table.*****)**

* allows you to insert a new row into a table.
* Its syntax is

```sql
INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …);
```

* Its return format is

```sql
INSERT oid count
```

* OID is an object identifier. PostgreSQL used the OID internally as a primary key for its system tables. Typically, the INSERT statement returns OID with value 0
* The count is the number of rows that the INSERT statement inserted successfully

#### Returning Clause

* The INSERT statement also has an optional RETURNING clause that returns the information of the inserted row.

**Example 1 (return the entire inserted row)**

```sql
INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING *;
```

**Example 2 (returns the id of the inserted row)**

```sql
INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING id;
```

**Example 3 (rename the returned value)**

```sql
INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING output_expression AS output_name;
```

#### Examples

**1. Inserting a single row into a table**

```sql
INSERT INTO links (url, name)
VALUES('https://google.com', 'Google');
```

It returns

```sql
INSERT 0 1
```

**2. Inserting character string that contains a single quote**

you have to use an additional single quote (') to escape it.

```sql
INSERT INTO links (url, name)
VALUES('http://www.oreilly.com','O''Reilly Media');
```

**3. Inserting a date value**

To insert a date value into a column with the DATE type, you use the date in the format 'YYYY-MM-DD'.

```sql
INSERT INTO links (url, name, last_update)
VALUES ('https://google.com', 'Google', '2013-06-01')
```

***

### Insert multiple rows

**(*****show you how to insert multiple rows into a table.*****)**

* To insert multiple rows into a table using a single INSERT statement, you use the following syntax:

```sql
INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);
```

#### Example (Inserting multiple rows and returning inserted rows)

```sql
INSERT INTO
    links(url,name, description)
VALUES
    ('https://duckduckgo.com/','DuckDuckGo','Privacy & Simplified Search Engine'),
    ('https://swisscows.com/','Swisscows','Privacy safe WEB-search')
RETURNING *;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FXKaNfIbZqbuw8TJZ4b8I%2Fimage.png?alt=media&#x26;token=b570cb52-9d26-4c6e-9e8e-8f6faf8b07ca" alt=""><figcaption></figcaption></figure>

### Update

**(*****update existing data in a table.*****)**

* The PostgreSQL UPDATE statement allows you to modify data in a table.
* Its syntax is

```sql
UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;
```

* it returns the following command tag

```sql
UPDATE count
```

* where count is the number of rows updated including rows whose values did not change.

#### Returning updated rows

It also have optional RETURNING clause like INSERT statement that returns the updated rows

```sql
UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition
RETURNING * | output_expression AS output_name;
```

#### Example (updating a row and returning the updated row)

```sql
UPDATE courses
SET published_date = '2020-07-01'
WHERE course_id = 2
RETURNING *;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FI080Ob1muTZOcB2SWshD%2Fimage.png?alt=media&#x26;token=d5032d51-97df-4e95-af16-2a2f4b03ca32" alt=""><figcaption></figcaption></figure>

### Update join

**(*****update values in a table based on values in another table.*****)**

* Sometimes, you need to update data in a table based on values in another table. In this case, you can use the PostgreSQL UPDATE join syntax as follows:

```sql
UPDATE t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;
```

#### Example

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FY903U4965w05l72DKtIu%2Fimage.png?alt=media&#x26;token=f495c62e-9476-43aa-8e24-583627f848b5" alt=""><figcaption></figcaption></figure>

Calculate the net price of every product based on the discount of the product segment

```sql
UPDATE
	product p
SET
	net_price = price - price * discount
FROM
	product_segment s
WHERE
	p.segment_id = s.id;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FJjUpp4dt2h4pBV4wZy5r%2Fimage.png?alt=media&#x26;token=bfde33a9-49f6-4326-96a2-4bc589c8c4aa" alt=""><figcaption></figcaption></figure>

### Delete

**(*****delete data in a table.*****)**

* It allows us to delete one or more rows from a table
* Its syntax is

```sql
DELETE FROM table_name
WHERE condition;
```

* The DELETE statement returns the number of rows deleted.
* To return the deleted row(s) to the client, you use the RETURNING clause as follows:

```sql
DELETE FROM table_name
WHERE condition
RETURNING (select_list | *)
```

#### Example

Deletes the rows with ids 6 and 7 and returns the deleted row to the client

```sql
DELETE FROM links
WHERE id IN (6, 7)
RETURNING *;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FAFfB5X0CGA8Hqwu37Nor%2Fimage.png?alt=media&#x26;token=1de84285-b120-427d-b828-9f2df03bc697" alt=""><figcaption></figcaption></figure>

### Upsert

**(*****insert or update data if the new row already exists in the table.*****)**

* The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).
* We can use the `INSERT ON CONFLICT` statement as follows

```sql
INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT target action;
```

* `target` can be
  * (column\_name) – a column name.
  * ON CONSTRAINT constraint\_name – where the constraint name could be the name of the UNIQUE constraint.
  * WHERE predicate – a WHERE clause with a predicate.
* `action` can be
  * DO NOTHING – means do nothing if the row already exists in the table.
  * DO UPDATE SET column\_1 = value\_1, .. WHERE condition – update some fields in the table.

#### Example

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FElQu8fWTVqsEHGFht9Lu%2Fimage.png?alt=media&#x26;token=3760cc78-58c5-4a11-a406-f3ab6d751f0c" alt=""><figcaption></figcaption></figure>

Concatenate the new email with old email when inserting a customer that already exists

```sql
INSERT INTO customers (name, email)
VALUES('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO
	UPDATE SET email = EXCLUDED.email || ';' || customers.email;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FKdWiaVKTggY9hrh8uQzA%2Fimage.png?alt=media&#x26;token=50feb551-8648-4ced-bfd5-71be28e1f70f" alt=""><figcaption></figcaption></figure>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://notes.nomanaziz.me/development/database/sql/basics/9.-modifying-data.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.