ALTER keyword is used for change table, add new columns update and add values to column or delete them ...
Published: 2024-04-14 10:54:34


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ybc2y2u0nzhq8mcbng0u.png)

## add new columns
To add two new columns to your table, you can use the `ALTER TABLE` statement in SQL. Here's an example of how you can add a `publish_date` column and a `genre` column to your table (assuming your table is named `books`):

```sql
ALTER TABLE books
ADD COLUMN publish_date DATE,
ADD COLUMN genre VARCHAR(255);
```

This SQL query adds a `publish_date` column that stores dates, and a `genre` column that can store strings up to 255 characters long. Remember to replace `DATE` and `VARCHAR(255)` with the appropriate data types that suit your needs for these new columns.

After running this query, the `publish_date` and `genre` columns will be added to the end of your table schema, and all rows will have `NULL` values in these new columns by default. You can then update these columns with actual values using the `UPDATE` statement.

If you need further assistance with updating the values or any other SQL operations, feel free to ask!


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8hsby27wi907tcxs7qat.png)


Certainly! Here are seven example dates that you can use for the `publish_date` column:

```plaintext
1999-04-20
2001-07-15
2003-09-10
2005-12-01
2008-03-23
2010-06-17
2012-11-02
```

To insert these dates into the `publish_date` column for existing rows in your table, you would use the `UPDATE` statement in SQL. Assuming you want to update the rows in the order they appear and your table is named `books`, here's how you could do it:

```sql
UPDATE books SET publish_date = '1999-04-20' WHERE bookid = 1;
UPDATE books SET publish_date = '2001-07-15' WHERE bookid = 2;
UPDATE books SET publish_date = '2003-09-10' WHERE bookid = 4;
UPDATE books SET publish_date = '2005-12-01' WHERE bookid = 5;
UPDATE books SET publish_date = '2008-03-23' WHERE bookid = 3;
UPDATE books SET publish_date = '2010-06-17' WHERE bookid = 6;
UPDATE books SET publish_date = '2012-11-02' WHERE bookid = 7;
```

This will assign each `publish_date` to the corresponding `bookid`. If you have a different criterion for assigning dates or if the `bookid` does not match the order you want, you'll need to adjust the `WHERE` clause accordingly.

Remember to execute these statements in your SQL database management system to update the records. If you need to insert the dates for new rows or have any other questions, let me know!

Yes, you can combine the `UPDATE` statements into a single query using a `CASE` expression. This allows you to update different rows with different values in one go. Here's how you can do it:

```sql
UPDATE books
SET publish_date = CASE
    WHEN bookid = 1 THEN '1999-04-20'
    WHEN bookid = 2 THEN '2001-07-15'
    WHEN bookid = 4 THEN '2003-09-10'
    WHEN bookid = 5 THEN '2005-12-01'
    WHEN bookid = 3 THEN '2008-03-23'
    WHEN bookid = 6 THEN '2010-06-17'
    WHEN bookid = 7 THEN '2012-11-02'
END
WHERE bookid IN (1, 2, 4, 5, 3, 6, 7);
```

This query will update the `publish_date` for each `bookid` listed in the `CASE` expression. The `WHERE` clause at the end is used to limit the update to only those rows that have a `bookid` mentioned in the `CASE` expression, which makes the operation more efficient. Remember to execute this statement in your SQL database management system to apply the changes.


## then when I want to RUN the query I got this error 
ERROR: column "publish_date" is of type date but expression is of type text

## solve 
The error message you're encountering indicates that the SQL database expects a date data type for the `publish_date` column, but the provided expressions are being interpreted as text. This can happen if the database does not automatically cast the string literals to date types.

To resolve this issue, you can explicitly cast the string literals to dates using the `CAST` function or by using the date literal syntax, depending on your SQL database system. Here's how you can modify your `UPDATE` statement to cast the strings to dates:

```sql
UPDATE books
SET publish_date = CASE
    WHEN bookid = 1 THEN CAST('1999-04-20' AS DATE)
    WHEN bookid = 2 THEN CAST('2001-07-15' AS DATE)
    WHEN bookid = 4 THEN CAST('2003-09-10' AS DATE)
    WHEN bookid = 5 THEN CAST('2005-12-01' AS DATE)
    WHEN bookid = 3 THEN CAST('2008-03-23' AS DATE)
    WHEN bookid = 6 THEN CAST('2010-06-17' AS DATE)
    WHEN bookid = 7 THEN CAST('2012-11-02' AS DATE)
END
WHERE bookid IN (1, 2, 4, 5, 3, 6, 7);
```

Alternatively, if you're using PostgreSQL, you can use the `::` operator to cast the strings:

```sql
UPDATE books
SET publish_date = CASE
    WHEN bookid = 1 THEN '1999-04-20'::DATE
    WHEN bookid = 2 THEN '2001-07-15'::DATE
    WHEN bookid = 4 THEN '2003-09-10'::DATE
    WHEN bookid = 5 THEN '2005-12-01'::DATE
    WHEN bookid = 3 THEN '2008-03-23'::DATE
    WHEN bookid = 6 THEN '2010-06-17'::DATE
    WHEN bookid = 7 THEN '2012-11-02'::DATE
END
WHERE bookid IN (1, 2, 4, 5, 3, 6, 7);
```

These modifications should help you avoid the type mismatch error by ensuring that the values for `publish_date` are treated as dates by the SQL engine. If you continue to experience issues, please let me know the specific SQL database system you're using, and I can provide further assistance.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyofasdf63hkmxb5mwq8.png)


## DELETE (DROP) column 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3pg7t8ztc8ee8gzayixh.png)