CODEWITHSUNDEEP
sql
clarifier
clarifier

Reputation: 149

how to undo a wrongly performed query which affected the records in SQL

I added a new column to already existed Table

alter table Employee
add  Emp_dept varchar(50)

try to insert columns in to table now but output was not as i was expected

insert into Employee(Emp_dept)
values ('Accounting'),
('Accounting'),
('Technical'),
('Technical'),
('Managing'),
('Managing');

Upvotes: 0

Views: 61

Answers (1)

Emmet
Emmet

Reputation: 6421

Assuming you began with a table:

Employee
+----+-------+
| ID | Name  |
+----+-------+
|  1 | Joe   |
|  2 | Bob   |
|  3 | Mary  |
+----+-------+

Then applied the ALTER TABLE, you probably obtained:

Employee
+----+-------+----------+
| ID | Name  | Emp_dept |
+----+-------+----------+
|  1 | Joe   |     NULL |
|  2 | Bob   |     NULL |
|  3 | Mary  |     NULL |
+----+-------+----------+

If you then performed the insert, and it worked at all, I expect you would then have something like:

Employee
+----+-------+------------+
| ID | Name  |   Emp_dept |
+----+-------+------------+
|  1 | Joe   |       NULL |
|  2 | Bob   |       NULL |
|  3 | Mary  |       NULL |
|  4 | NULL  | Accounting |
|  5 | NULL  | Accounting |
|  6 | NULL  | Technical  |
|  7 | NULL  | Technical  |
|  8 | NULL  | Managing   |
|  9 | NULL  | Managing   |
+----+-------+------------+

Here I've assumed that ID is an autoincrement column and Name is NULLable.

To reverse this, you could try:

DELETE FROM Employee WHERE Emp_dept IS NOT NULL

Followed by:

ALTER TABLE Employee DROP COLUMN Emp_dept

This should return you to where you were prior to adding the column, which appears to be what you want.

Upvotes: 2

Related Questions