Solved

Fill blank spaces in SQL table

Posted on 2009-06-30
9
256 Views
Last Modified: 2012-08-14
Hi

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?

Thanks
0
Comment
Question by:edjones1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Assisted Solution

by:jwenting
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.
0
 
LVL 5

Assisted Solution

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

update t1 set field2 = field1 where field2 is null

regards,

0
 
LVL 60

Accepted Solution

by:
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

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 18

Assisted Solution

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

UPDATE YourTable
SET Field2 = Field1
WHERE Field2 = ''
0
 
LVL 18

Expert Comment

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

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

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!

0
 
LVL 60

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...
0
 
LVL 18

Expert Comment

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

Expert Comment

by:shru_0409
ID: 24744901
nvl(field2,field1)
or
nullif(field1,field2)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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