Fill blank spaces in SQL table

Posted on 2009-06-30
Last Modified: 2012-08-14

I've got 2 columns fields in a table.

Field1 is awlays populated with data
Field 2 is mostly blank.

How do I populate field2 with data from field 1 if field 2 is empty, but only if field2 is empty?

Question by:edjones1

Assisted Solution

jwenting earned 100 total points
ID: 24744208
You want to do that on inserting a record or at a later date?
If the former, use a trigger on post-insert. See documentation for your database engine on how to implement triggers.

If the latter, a simple update query will do:
update mu_table set column2 = column1 where column2 is null;

commit that and you're done.

Assisted Solution

mnialon earned 100 total points
ID: 24744212
please try the following :

update t1 set field2 = field1 where field2 is null


LVL 59

Accepted Solution

Kevin Cross earned 200 total points
ID: 24744260
The above are correct.  However, if your field can be empty string '' instead of null, then just adjust like this:
update your_table_name

set field2 = field1

where isnull(field2, '') = ''

Open in new window

LVL 18

Assisted Solution

brejk earned 100 total points
ID: 24744292
If blank means an empty string:

UPDATE YourTable
SET Field2 = Field1
WHERE Field2 = ''
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 18

Expert Comment

ID: 24744295
@mwvisa1: If field2 is indexed the better would be:

update your_table_name
set field2 = field1
where field2 = '' or field2 is null
LVL 59

Expert Comment

by:Kevin Cross
ID: 24744317
I typically don't worry about performance on one time data cleanup activities, but you are correct that should always code to take advantage of indexes.  As a programmer, I like checking for NULL first then compare as string, BTW. :) habit!

LVL 59

Expert Comment

by:Kevin Cross
ID: 24744329
@brejk: And I should correct that, I don't worry on simple stuff like this. :) I would be in trouble if I ran anything too nasty on a production system...
LVL 18

Expert Comment

ID: 24744344
@mwvisa1: What a sad world would be without our habits ;-)
LVL 14

Expert Comment

ID: 24744901

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Determine next b-weekly date 12 56
Save sql query result in sql server 15 25
encyps queries mssql 15 27
Creating Alerts in sql sever 2 13
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now