CODEWITHSUNDEEP
sqlsql-server
Morez
Morez

Reputation: 2186

Sql Server Update: Set column values by copying excel sheet data

I have created simple Car Table which has around 1500 records.

Existing Table :

+-------+---------+--------------+
|name   | plural                 |
|-------|------------------------|
|audi   | audis                  |
|bmw    | null                   |
|acura  | acura sports models    |
| ...   | ...                    | 
+-------+---------+--------------+

Expected Output: 

+-------+---------+--------------+
|name   | plural                 |
|-------|------------------------|
|audi   | audis                  |
|bmw    | bmws                   |
|acura  | acura sports models    |
|suzuki | suzukis                | 
+-------+---------+--------------+

I want to update values which has null value. I have Excel file which has name and plural data. however I can create list for name and plural. Is there any way to update plural in one go.

I am looking for something like not sure whether it is possible or not

Update car 
SET plural = LIST
WHERE name = LIST

Upvotes: 0

Views: 351

Answers (2)

Stephen
Stephen

Reputation: 1542

first you will need to import that excel file into a dummy table.

Right click DB => Tasks => Import Data.... then follow prompts.

Then do a normal update statement.

Update c
SET plural = d.plural 
FROM car c
JOIN <DummyTableName> d ON d.name = c.name
WHERE plural IS NULL

As you don't want to import data.

Excel file Data : 

+---+-------+---------------------+-----------------------------------+
|   |A      | B                   | C                                 |
|---+-------|---------------------+-----------------------------------|
|  1|audi   | audis               | =CONCANATE("('"&A1&"','"&B1&"'),")|
|  2|bmw    | bmws                |     Drag to Bottom for all rows   |
|  3|acura  | acura sports models |                                   |
|  4|...    | ...                 |                                   | 
+---+-------+---------------------+-----------------------------------+

You may just add an extra column with this ="('"&A1&"','"&B1&"')," where A1 is you name and B1 is plural, copy that....

Update c
SET plural = d.plural 
FROM car c
JOIN (VALUES ('Audi', 'Audis'),
('BMW', 'BMWs')...) d(name, plural) ON d.name = c.name
WHERE plural IS NULL

Upvotes: 0

John Woo
John Woo

Reputation: 263733

Here, try this:

Update car 
SET plural = name 
WHERE plural IS NULL

Upvotes: 2

Related Questions