Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Updating a field

Author  Topic 

andypgill
Starting Member

34 Posts

Posted - 2013-02-06 : 12:22:11
Hi

Can anyone help me with my code.

I have a table called [Draft Version] with a field called draft. This field contains one number.

My main table is called budgets and it contains a field called draft.

I basically want to updated the budgets.draft with the number that is in [Draft Version] if the budgets.draft is null.

This is my code

UPDATE Budgets
SET draft = (SELECT [Draft] FROM [Draft Version]
where Budget.draft is null)

The problem I'm getting is that it is updating the field irrespective of whether it is null or not.

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-06 : 12:45:20
Is there just one row in each table? Assuming that there are many rows in the Budget table that are NULL and there are many rows in [Draft Version] where draft is something other than NULL, which values from the [Draft Version] should go into which rows in Budget table? In other words, is there another column (or may be more than one column) that can be used to relate a given row in the Budget table to a row in [Draft Version] table?
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-06 : 14:25:56
If there is no related fields you could try:

UPDATE B
SET Draft = (SELECT TOP(1) [Draft] FROM [Draft Version])
FROM Budgets
WHERE Draft IS NULL


djj
Go to Top of Page

andygill
Starting Member

3 Posts

Posted - 2013-02-07 : 02:13:50
quote:
Originally posted by James K

Is there just one row in each table? Assuming that there are many rows in the Budget table that are NULL and there are many rows in [Draft Version] where draft is something other than NULL, which values from the [Draft Version] should go into which rows in Budget table? In other words, is there another column (or may be more than one column) that can be used to relate a given row in the Budget table to a row in [Draft Version] table?



There is just one row and one field in [draft version] there will be many rows in budget which will have a null draft field.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 04:59:42
Since there is only one row in [Draft Version], djj's code should work.
Go to Top of Page
   

- Advertisement -