Solved

Fill blank spaces in SQL table

Posted on 2009-06-30
9
251 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
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 59

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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 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!

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 31
T-SQL Update Table using Input Parameter as Column Name 6 40
subtract 1 in Access 2003 query 7 37
Sql Query Datatype 2 18
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 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

23 Experts available now in Live!

Get 1:1 Help Now