SQL Update question

Hi,


Is there a fairly simple SQL update statement that can be used to update
one field in all rows by filling it with the value from another field in
that same row?


Like:

"UPDATE tableName SET fieldA = fieldB WHERE fieldA IS NULL"

I know this doesn't work, but is there a simple way like that do it?

I'm adding a field to a pre-existing table, and about 90% of the 900
rows need this new field to simply mirror the ID field.

Thanks.

Re: SQL Update question

Travis Pupkin wrote:
> Hi,
>
>
> Is there a fairly simple SQL update statement that can be used to
> update one field in all rows by filling it with the value from
> another field in that same row?
>
>
> Like:
>
> "UPDATE tableName SET fieldA = fieldB WHERE fieldA IS NULL"
>
> I know this doesn't work, but is there a simple way like that do it?
>
> I'm adding a field to a pre-existing table, and about 90% of the 900
> rows need this new field to simply mirror the ID field.
>
> Thanks.

I can't answer this without knowing the type and version of your database.
Please include that information in all your future posts. It is almost
always relevant.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Update question

In article , reb01501
@NOyahoo.SPAMcom says...
> Travis Pupkin wrote:
> > Hi,
> >
> >
> > Is there a fairly simple SQL update statement that can be used to
> > update one field in all rows by filling it with the value from
> > another field in that same row?
> >
> >
> > Like:
> >
> > "UPDATE tableName SET fieldA = fieldB WHERE fieldA IS NULL"


> I can't answer this without knowing the type and version of your database.
> Please include that information in all your future posts. It is almost
> always relevant.


Sorry. SQL Server 2000.

Re: SQL Update question

Travis Pupkin wrote:
> In article , reb01501
> @NOyahoo.SPAMcom says...
>> Travis Pupkin wrote:
>>> Hi,
>>>
>>>
>>> Is there a fairly simple SQL update statement that can be used to
>>> update one field in all rows by filling it with the value from
>>> another field in that same row?
>>>
>>>
>>> Like:
>>>
>>> "UPDATE tableName SET fieldA = fieldB WHERE fieldA IS NULL"
>
>
>> I can't answer this without knowing the type and version of your
>> database. Please include that information in all your future posts.
>> It is almost always relevant.
>
>
> Sorry. SQL Server 2000.

Wait a minute. I misread your question to read " .. how to update a field in
one table from a field in another table"

What's wrong with the SQL statement you posted? Didn't you try it?

That's the exact syntax you should use, unless datatype conversions are
involved.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Update question

In article , reb01501
@NOyahoo.SPAMcom says...
> Travis Pupkin wrote:
> > In article , reb01501
> > @NOyahoo.SPAMcom says...
> >> Travis Pupkin wrote:
> >>> Hi,
> >>>
> >>>
> >>> Is there a fairly simple SQL update statement that can be used to
> >>> update one field in all rows by filling it with the value from
> >>> another field in that same row?
> >>>
> >>>
> >>> Like:
> >>>
> >>> "UPDATE tableName SET fieldA = fieldB WHERE fieldA IS NULL"
> >
> >
> >> I can't answer this without knowing the type and version of your
> >> database. Please include that information in all your future posts.
> >> It is almost always relevant.
> >
> >
> > Sorry. SQL Server 2000.
>
> Wait a minute. I misread your question to read " .. how to update a field in
> one table from a field in another table"
>
> What's wrong with the SQL statement you posted? Didn't you try it?
>
> That's the exact syntax you should use, unless datatype conversions are
> involved.


That's what I thought, but it says the query executed successfully (I'm
running it in the SQL2000 manager SQL pane) but the one test record is
not updated afterward (I plan eventually to use it on several hundred
rows).

Just to be clear, I want to fill a null field in one row with a value
grabbed from another field in that same row. I thought maybe it doesn't
work because first I might need to query the row for the value of fieldb
and then update the empty field (fielda) in a nested sql statement, but
I'm hoping there's a simpler way to do it. Glad I was on the right
track, but it's not working nonetheless.

Re: SQL Update question

Travis Pupkin wrote:
> In article , reb01501
> @NOyahoo.SPAMcom says...
>> Travis Pupkin wrote:
>>> In article , reb01501
>>> @NOyahoo.SPAMcom says...
>>>> Travis Pupkin wrote:
>>>>> Hi,
>>>>>
>>>>>
>>>>> Is there a fairly simple SQL update statement that can be used to
>>>>> update one field in all rows by filling it with the value from
>>>>> another field in that same row?
>>>>>
>>>>>
>>>>> Like:
>>>>>
>>>>> "UPDATE tableName SET fieldA = fieldB WHERE fieldA IS NULL"
>>>
>>>
>>>> I can't answer this without knowing the type and version of your
>>>> database. Please include that information in all your future posts.
>>>> It is almost always relevant.
>>>
>>>
>>> Sorry. SQL Server 2000.
>>
>> Wait a minute. I misread your question to read " .. how to update a
>> field in one table from a field in another table"
>>
>> What's wrong with the SQL statement you posted? Didn't you try it?
>>
>> That's the exact syntax you should use, unless datatype conversions
>> are involved.
>
>
> That's what I thought, but it says the query executed successfully
> (I'm running it in the SQL2000 manager SQL pane) but the one test
> record is
> not updated afterward (I plan eventually to use it on several hundred
> rows).
>
> Just to be clear, I want to fill a null field in one row with a value
> grabbed from another field in that same row. I thought maybe it
> doesn't
> work because first I might need to query the row for the value of
> fieldb
> and then update the empty field (fielda) in a nested sql statement,
> but
> I'm hoping there's a simpler way to do it. Glad I was on the right
> track, but it's not working nonetheless.

Verify that your filter criterion is correct by converting the update query
to a select statement with the same WHERE clause. Run the query in query
analyzer and make sure if returns your test row.

NOTE: if FieldA is a char or varchar datatype, '' (empty string) does not
equal NULL.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Update question

In article , reb01501
@NOyahoo.SPAMcom says...

> Verify that your filter criterion is correct by converting the update query
> to a select statement with the same WHERE clause. Run the query in query
> analyzer and make sure if returns your test row.
>
> NOTE: if FieldA is a char or varchar datatype, '' (empty string) does not
> equal NULL.
>
> Bob Barrows

Thanks Bob. That must be the problem. It's a varchar field. For some
reason I thought I used NULL earlier in a different query and it worked,
but I guess not. I'll use LIKE '' instead.