Update a field with data from another field of the same table

[Beginner]

I have a table with two fields SID No-Rep, SID.
If field Split Type is NOT NULL, then values in
SID No-Rep should be updated with SID values.

The following Update query fills doesnot update the values
from the field in the required records. Instead the update
is like [BILLING].SID

UPDATE [BILLING] SET [BILLING].[SID No-Rep] = (SELECT SID
FROM [BILLING])
WHERE ((([BILLING].[Split Type]) Is Null));

Any help is appreciated

Thanks in advance
Beginner

 

[Dennis Schmidt]

Hi ,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

On the UpdateTo line of the column that you wish to update, use an IIF()
statement similar to the following:

IIF(Conditional Statement, True, False)

IIf(Not IsNull([Split Type]),[Sid])

Ordinarily an IIF statement has a true and false argument, but in this case
you only want to perform the true part of the argument so no false part is
included.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support

 

[Tom Ellison]

Dear Beginner:

I think perhaps you are saying you want to update all the rows in
which [Split Type] IS NULL. For those rows you want to set [SID
No-Rep] to the value of SID in that same row.

If that is correct, the query would look like this:

UPDATE BILLING SET [SID No-Rep] = SID
WHERE [Split Type] IS NULL

You do not need the subquery "SELECT SID FROM BILLING" at all for
this. In fact, such a subquery when not correlated or otherwise
filtered to only a single row does not return a singe value (a scalar
value) and would not be usable (since you can only set the [SID
No-Rep] column to a single value in each instance addressed by the
query). That assumes, of course, that BILLING may have more than one
row.

The above explanation may seem confusing if you truly are a Beginner,
but I thought it best to be a bit thorough. Don't be confused by
that. But if the solution I propose is what you wanted, perhaps it's
best to just study that.

I have a table with two fields SID No-Rep, SID.
If field Split Type is NOT NULL, then values in
SID No-Rep should be updated with SID values.

The following Update query fills doesnot update the values
from the field in the required records. Instead the update
is like [BILLING].SID

UPDATE [BILLING] SET [BILLING].[SID No-Rep] = (SELECT SID
FROM [BILLING])
WHERE ((([BILLING].[Split Type]) Is Null));

Any help is appreciated

Thanks in advance
Beginner

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Beginner]

Thanks, it works - but only if SID is written as [SID]
It could be because the field type of SID is Text

Beginner

-----Original Message-----
Dear Beginner:

I think perhaps you are saying you want to update all the rows in
which [Split Type] IS NULL. For those rows you want to set [SID
No-Rep] to the value of SID in that same row.

If that is correct, the query would look like this:

UPDATE BILLING SET [SID No-Rep] = SID
WHERE [Split Type] IS NULL

You do not need the subquery "SELECT SID FROM BILLING" at all for
this. In fact, such a subquery when not correlated or otherwise
filtered to only a single row does not return a singe value (a scalar
value) and would not be usable (since you can only set the [SID
No-Rep] column to a single value in each instance addressed by the
query). That assumes, of course, that BILLING may have more than one
row.

The above explanation may seem confusing if you truly are a Beginner,
but I thought it best to be a bit thorough. Don't be confused by
that. But if the solution I propose is what you wanted, perhaps it's
best to just study that.

I have a table with two fields SID No-Rep, SID.
If field Split Type is NOT NULL, then values in
SID No-Rep should be updated with SID values.

The following Update query fills doesnot update the values
from the field in the required records. Instead the update
is like [BILLING].SID

UPDATE [BILLING] SET [BILLING].[SID No-Rep] = (SELECT SID
FROM [BILLING])
WHERE ((([BILLING].[Split Type]) Is Null));

Any help is appreciated

Thanks in advance
Beginner

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
.