CODEWITHSUNDEEP
sqlsql-serversql-update
LuckySlevin
LuckySlevin

Reputation: 745

How to remove unwanted rows and create new column in SQL?

I have two tables.

My first table is called as WORLD and this table have a column named PEOPLE. In PEOPLE table I have both women's and men's names.

My second table is called as MEN and this table has a column named NAMES. It consists of men's names.

What I want to do is by comparing those two tables finding women's names and adding them to a column named "WOMEN" in the WORLD table.

WORLD.PEOPLE             MEN.NAMES

John                John
Joe                 Alan
Jessica             Michael
Martin              Martin
Alan                Adam
Eva                 Joe
Mary

What I want to have is:

WORLD.PEOPLE             WORLD.WOMEN

John                Jessica
Joe                 Eva
Jessica             Mary
Martin              
Alan                
Eva                 
Mary

I tried using this statement:

SELECT People FROM WORLD WHERE (People NOT IN(SELECT Names FROM MEN)))

However this only gives result, it doesn't update the WORLD table.

What should I do?

Upvotes: 1

Views: 359

Answers (3)

LuckySlevin
LuckySlevin

Reputation: 745

You should try to Create a new table and add the values there and then drop the WORLD table. I guess you just want to work on one single database, but i can't think any other easier way. With insert you will get null values so it won't be practical. You can try what i said.

Upvotes: 2

frabala
frabala

Reputation: 369

INSERT INTO world(women) SELECT People FROM WORLD WHERE (People NOT IN(SELECT Names FROM MEN)))

or something like that? Also, see the documentation. I'm not sure if the above syntax is correct, because you're inserting data to a table by selecting data from the same table. Maybe you need to use "AS".

Upvotes: 0

Alexander Shell
Alexander Shell

Reputation: 301

You must have some key field in table WORLD for identification records. Or use another table for store women names.

Upvotes: 1

Related Questions