473,597 Members | 2,290 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Updating a table starting with the first empty column in a specific row

5 New Member
Hi,
I'm struggling to find a solution to this (very likely simple) problem:

I have 2 tables (say A and B). I want to populate some rows in B with information from A. I want the update query to start from the first row were column1 is empty, downwards. Tried to use cursor but wasn't successful. Any help is much appreciated.
Aug 4 '10 #1
4 2516
gpl
152 New Member
What do you mean by downwards ? There is no* default order in a table, what sort order do you really mean ?

*actually, there is the order that the rows were inserted, but if you have a clustered index then that order would override the insertion order
Aug 5 '10 #2
ck9663
2,878 Recognized Expert Specialist
Try something like:

Expand|Select|Wrap|Line Numbers
  1. UPDATE A
  2. SET
  3. A.COL1 = ISNULL(A.COL1, B.COL1),
  4. A.COL2 = ISNULL(A.COL2, B.COL2),
  5. A.COL3 = ISNULL(A.COL3, B.COL3) 
  6. FROM TABLEA A
  7. INNER JOIN TABLEB B ON A.KEY = B.KEY
  8.  
Now, this code will only update your column if it's currently NULL. Technically, it will update even if it's not null. However, if it's not null, it will update with the current value.

Good Luck!!!

~~ CK
Aug 5 '10 #3
NeoPa
32,566 Recognized Expert Moderator MVP
If I understand your question correctly then I'm not sure if this could be done using SQL alone.

If it were an Access question I'd say to use recordset processing in VBA. Something similar may be possible with a cursor in T-SQL, but I wouldn't know if it is. Alternatively, newer versions of MS SQL Server give the facility to link in .NET code from any of the .NET languages using the Common Language Runtime (or CLR). I'm confident this could work for you, but I'm afraid my experience stops short of being much help along those lines.

Welcome to Bytes!
Aug 5 '10 #4
Jerry Winston
145 Recognized Expert New Member
I could tell you how to do this if I knew which table column1 belongs to. I think you're going for something like this (psudo-code):
Expand|Select|Wrap|Line Numbers
  1. WHILE B.lastrow = FALSE
  2.    IF B.column1 = NULL THEN
  3.       B.column1 = A.columnN(rowNumber)
  4.       rowNumber++
  5.    END IF 
  6. WEND
please be very specific about which field(column) is NULL and from where you would like to get data to update it.
Aug 10 '10 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
32343
by: Caroline | last post by:
I need to update a table by copying a column from another table (having the same structure, but on another database), from the record having the same primary key. 1 - What is the correct query? 2 - I tried copying them record by record, but the datatype is ntext, (it displays <long
1
7491
by: Alain Filiatrault | last post by:
HI, I have an SQL Server table with only 1 column. That column is an identity column. How can I insert a row in this table using SQL syntax? I tried insert into T_tableName () values () and a few other options, but I can't seem to get it to insert.
3
7296
by: Carmine | last post by:
I have to add a date column to a db2/zos table with a blob column. Can I rename the blob table to old, create a new table with the date column, then insert/select the data from the old table? Do I have to select data from the main table and the aux table or is the aux table done automatically? What's the best way to handle this?
1
1466
by: sho_nuff | last post by:
Hello all, Is it possible to have column specific context menus? so, if i have three columns containing different objects / info, i could have a context menu corresponding to each one? Thanks, SN
2
1732
by: GroZZleR | last post by:
Hey all, How do things like Windows Explorer and other applications get an empty column at the end of a data grid that stretches the entire width? Look at this picture: http://www.grozzler.com/dump/dt_want.png See how that final column is empty and stretches the entire width instead of leaving a giant hole like this:
1
6332
by: Random | last post by:
I don't understand why this error is happening when I try to load my xml into my DataSet. "Cannot add a nested relation or an element column to a table containing a SimpleContent column." Can anyone help direct me towards figuring out the cause. Yes, the xml is big, but not too complex.
6
10873
by: Remaniak | last post by:
Hi all, I am using Access 2003. I am trying to insert a column "test" in "data table" right after the column "category". My code adds the column "test", but if I add the AFTER statement it tells me: "syntax error in ALTER TABLE statement" DoCmd.RunSQL "ALTER TABLE ADD test text AFTER category;"
1
2078
by: Pydimarri Srinivas | last post by:
Hi All, I am trying to purge the data from a DB2 table having identity column defined usind DB2 utilitties. I am getting the following error "DSNU269I .............Field is invalid" "The error says that “Identity columns that were defined as GENERATED always cannot be specified in a LOAD field specification list, nor can these columns be implied in LOAD format UNLOAD or LOAD with no field specification list”" Please let me know how...
3
3272
by: madhavi123 | last post by:
Hi All, I have a to insert a set of rows into a table which is on some other server, and that table has an identity column which is also a primary key of that column. I am getting the error 'Table 'myremoteserver.myremotedatabase.dbo.myremotetable' does not exist or cannot be opened for SET operation' if the statement SET IDENTITY_INSERT 'myremoteserver.myremotedatabase.dbo.myremotetable' on is written before the insert statement. ...
3
1402
by: SANJINJIT | last post by:
Hi, I have a spreadsheet looks like this: QID C1 C2 C3 C4 %a %b %c %d 13123 b b b b 0% 100% 0% 0% 13124 d d d d 0% 0% 0% 100% 13125 d d d d 0% 0% 0% 100%
0
7970
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7887
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8036
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8259
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
3886
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3930
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2404
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1494
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.