updating data from different table

6 Sep 2006 11:47 AM

Manish Sukhija

Hi guys,
           i've a strange problem that i define below
I've 2 tables in which from first table i want to take data from a specific
filed and i want to update data of specific field of second table.
                    It's very easy to update data of one column but how
should i updata value for a series of column for example:

update MBPRequestMain_TEMP
set CreatedBy =(select UserId from MobileBillUserDetails)
update MBPRequestMain_TEMP
set RequestFor =(select MobileOwnerName from MobileBillUserDetails)
update MBPRequestMain_TEMP
set ModifiedBy =(select Entrid from MobileBillUserDetails)

               I don't want to run update statement seprately, which i was
doing above is there any way in which i can clubs this stmt. like wise

update MBPRequestMain_TEMP
set CreatedBy,RequestFor,ModifiedBy   =(select UserId,MobileOwnerName,Entrid
from MobileBillUserDetails)

               I'm giving this query but it's giving error, plz make me
correct where i'm wrong.

6 Sep 2006 11:59 AM

Hari Prasad

Hi,

If your consition is different you may need to write seperate update
statements based on the criteria.

Thanks
Hari
SQL Server MVP

Show quote

"Manish Sukhija" <ManishSukh***@discussions.microsoft.com> wrote in message
news:CB133545-50B7-4CE1-AC39-9E9853EC7D92@microsoft.com...
> Hi guys,
>           i've a strange problem that i define below
> I've 2 tables in which from first table i want to take data from a
> specific
> filed and i want to update data of specific field of second table.
>                    It's very easy to update data of one column but how
> should i updata value for a series of column for example:
>
> update MBPRequestMain_TEMP
> set CreatedBy =(select UserId from MobileBillUserDetails)
> update MBPRequestMain_TEMP
> set RequestFor =(select MobileOwnerName from MobileBillUserDetails)
> update MBPRequestMain_TEMP
> set ModifiedBy =(select Entrid from MobileBillUserDetails)
>
>               I don't want to run update statement seprately, which i was
> doing above is there any way in which i can clubs this stmt. like wise
>
> update MBPRequestMain_TEMP
> set CreatedBy,RequestFor,ModifiedBy   =(select
> UserId,MobileOwnerName,Entrid
> from MobileBillUserDetails)
>
>               I'm giving this query but it's giving error, plz make me
> correct where i'm wrong.
>
>
>
>
>
>

6 Sep 2006 12:07 PM

Manish Sukhija

Hi hari,
My condition is same like
select
UserId,MobileOwnerName,UserId,OwnerOrgId,UserId,UserId,OwnerOrgId,EmpNumber
from MobileBillUserDetails where mobilenum='6066112'
Thsi is condition where i've to get data

Show quote

"Hari Prasad" wrote:

> Hi,
>
> If your consition is different you may need to write seperate update
> statements based on the criteria.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Manish Sukhija" <ManishSukh***@discussions.microsoft.com> wrote in message
> news:CB133545-50B7-4CE1-AC39-9E9853EC7D92@microsoft.com...
> > Hi guys,
> >           i've a strange problem that i define below
> > I've 2 tables in which from first table i want to take data from a
> > specific
> > filed and i want to update data of specific field of second table.
> >                    It's very easy to update data of one column but how
> > should i updata value for a series of column for example:
> >
> > update MBPRequestMain_TEMP
> > set CreatedBy =(select UserId from MobileBillUserDetails)
> > update MBPRequestMain_TEMP
> > set RequestFor =(select MobileOwnerName from MobileBillUserDetails)
> > update MBPRequestMain_TEMP
> > set ModifiedBy =(select Entrid from MobileBillUserDetails)
> >
> >               I don't want to run update statement seprately, which i was
> > doing above is there any way in which i can clubs this stmt. like wise
> >
> > update MBPRequestMain_TEMP
> > set CreatedBy,RequestFor,ModifiedBy   =(select
> > UserId,MobileOwnerName,Entrid
> > from MobileBillUserDetails)
> >
> >               I'm giving this query but it's giving error, plz make me
> > correct where i'm wrong.
> >
> >
> >
> >
> >
> >
>
>
>

6 Sep 2006 1:09 PM

Hari Prasad

Hi,

Your update will look some thing like below; but main condition needs to be
chnaged based on your key column. If the phone number is the matching
condition go ahead and do the update with a Begin Tran.
Before commit ensure that you are updating the right rows..

update MBPRequestMain_TEMP
set CreatedBy = a.userid,
RequestFor = a.MobileOwnerName,
ModifiedBy =a.Entrid
From MobileBillUserDetails a
Where a.mobilenum = MBPRequestMain_TEMP.MBPRequestMain_TEMP
and a.mobilenum='6066112'

Thanks
Hari
SQL Server MVP

Show quote

"Manish Sukhija" <ManishSukh***@discussions.microsoft.com> wrote in message
news:B7260632-D266-49DF-BD92-4805FB1A5FDB@microsoft.com...
> Hi hari,
>          My condition is same like
> select
> UserId,MobileOwnerName,UserId,OwnerOrgId,UserId,UserId,OwnerOrgId,EmpNumber
> from MobileBillUserDetails where mobilenum='6066112'
>            Thsi is condition where i've to get data
>
>
> "Hari Prasad" wrote:
>
>> Hi,
>>
>> If your consition is different you may need to write seperate update
>> statements based on the criteria.
>>
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Manish Sukhija" <ManishSukh***@discussions.microsoft.com> wrote in
>> message
>> news:CB133545-50B7-4CE1-AC39-9E9853EC7D92@microsoft.com...
>> > Hi guys,
>> >           i've a strange problem that i define below
>> > I've 2 tables in which from first table i want to take data from a
>> > specific
>> > filed and i want to update data of specific field of second table.
>> >                    It's very easy to update data of one column but how
>> > should i updata value for a series of column for example:
>> >
>> > update MBPRequestMain_TEMP
>> > set CreatedBy =(select UserId from MobileBillUserDetails)
>> > update MBPRequestMain_TEMP
>> > set RequestFor =(select MobileOwnerName from MobileBillUserDetails)
>> > update MBPRequestMain_TEMP
>> > set ModifiedBy =(select Entrid from MobileBillUserDetails)
>> >
>> >               I don't want to run update statement seprately, which i
>> > was
>> > doing above is there any way in which i can clubs this stmt. like wise
>> >
>> > update MBPRequestMain_TEMP
>> > set CreatedBy,RequestFor,ModifiedBy   =(select
>> > UserId,MobileOwnerName,Entrid
>> > from MobileBillUserDetails)
>> >
>> >               I'm giving this query but it's giving error, plz make me
>> > correct where i'm wrong.
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>>

6 Sep 2006 12:02 PM

Dan Guzman

> update MBPRequestMain_TEMP
> set CreatedBy =(select UserId from MobileBillUserDetails)

The main problem here is that you have no correlation between the table to
be updated and the subquery expression. You need to add a WHERE clause to
the subquery so that it is properly correlated and no more than one row is
returned. Untested example:

UPDATE MBPRequestMain_TEMP
SET CreatedBy =
    (
    SELECT UserId
    FROM MobileBillUserDetails
    WHERE MobileBillUserDetails.KeyColumn = MBPRequestMain_TEMP.KeyColumn
    ),
    RequestFor = (
    SELECT MobileOwnerName
    FROM MobileBillUserDetails
    WHERE MobileBillUserDetails.KeyColumn = MBPRequestMain_TEMP.KeyColumn
    ),
    ModifiedBy = (
    SELECT Entrid
    FROM MobileBillUserDetails
    WHERE MobileBillUserDetails.KeyColumn = MBPRequestMain_TEMP.KeyColumn
    )

You can also accomplish the same result using the proprietary MSSQL
UPDATE...FROM syntax. Untested example:

UPDATE t
SET CreatedBy = t.UserId,
    RequestFor = t.MobileOwnerName
    ModifiedBy = t.Entrid
FROM MBPRequestMain_TEMP AS t
JOIN MobileBillUserDetails ON
    MobileBillUserDetails.KeyColumn = t.KeyColumn

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Manish Sukhija" <ManishSukh***@discussions.microsoft.com> wrote in message
news:CB133545-50B7-4CE1-AC39-9E9853EC7D92@microsoft.com...
> Hi guys,
>           i've a strange problem that i define below
> I've 2 tables in which from first table i want to take data from a
> specific
> filed and i want to update data of specific field of second table.
>                    It's very easy to update data of one column but how
> should i updata value for a series of column for example:
>
> update MBPRequestMain_TEMP
> set CreatedBy =(select UserId from MobileBillUserDetails)
> update MBPRequestMain_TEMP
> set RequestFor =(select MobileOwnerName from MobileBillUserDetails)
> update MBPRequestMain_TEMP
> set ModifiedBy =(select Entrid from MobileBillUserDetails)
>
>               I don't want to run update statement seprately, which i was
> doing above is there any way in which i can clubs this stmt. like wise
>
> update MBPRequestMain_TEMP
> set CreatedBy,RequestFor,ModifiedBy   =(select
> UserId,MobileOwnerName,Entrid
> from MobileBillUserDetails)
>
>               I'm giving this query but it's giving error, plz make me
> correct where i'm wrong.
>
>
>
>
>
>

6 Sep 2006 1:11 PM

Alexander Kuznetsov

Dan,

I think your update need a WHERE clause:

UPDATE MBPRequestMain_TEMP
SET CreatedBy =
    (
    SELECT UserId
    FROM MobileBillUserDetails
    WHERE MobileBillUserDetails.KeyColumn =
MBPRequestMain_TEMP.KeyColumn
    ),
    RequestFor = (
    SELECT MobileOwnerName
    FROM MobileBillUserDetails
    WHERE MobileBillUserDetails.KeyColumn =
MBPRequestMain_TEMP.KeyColumn
    ),
    ModifiedBy = (
    SELECT Entrid
    FROM MobileBillUserDetails
    WHERE MobileBillUserDetails.KeyColumn =
MBPRequestMain_TEMP.KeyColumn
    )
WHERE EXISTS(SELECT 1     FROM MobileBillUserDetails
    WHERE MobileBillUserDetails.KeyColumn =
MBPRequestMain_TEMP.KeyColumn
)

Also most likely the UPDATE ... FROM will perform much better.

6 Sep 2006 2:16 PM

Manish Sukhija

Thanks a lot Dan, it is working fine absoultely

Show quote

"Dan Guzman" wrote:

> > update MBPRequestMain_TEMP
> > set CreatedBy =(select UserId from MobileBillUserDetails)
>
> The main problem here is that you have no correlation between the table to
> be updated and the subquery expression. You need to add a WHERE clause to
> the subquery so that it is properly correlated and no more than one row is
> returned. Untested example:
>
> UPDATE MBPRequestMain_TEMP
> SET CreatedBy =
>     (
>     SELECT UserId
>     FROM MobileBillUserDetails
>     WHERE MobileBillUserDetails.KeyColumn = MBPRequestMain_TEMP.KeyColumn
>     ),
>     RequestFor = (
>     SELECT MobileOwnerName
>     FROM MobileBillUserDetails
>     WHERE MobileBillUserDetails.KeyColumn = MBPRequestMain_TEMP.KeyColumn
>     ),
>     ModifiedBy = (
>     SELECT Entrid
>     FROM MobileBillUserDetails
>     WHERE MobileBillUserDetails.KeyColumn = MBPRequestMain_TEMP.KeyColumn
>     )
>
> You can also accomplish the same result using the proprietary MSSQL
> UPDATE...FROM syntax. Untested example:
>
> UPDATE t
> SET CreatedBy = t.UserId,
>     RequestFor = t.MobileOwnerName
>     ModifiedBy = t.Entrid
> FROM MBPRequestMain_TEMP AS t
> JOIN MobileBillUserDetails ON
>     MobileBillUserDetails.KeyColumn = t.KeyColumn
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Manish Sukhija" <ManishSukh***@discussions.microsoft.com> wrote in message
> news:CB133545-50B7-4CE1-AC39-9E9853EC7D92@microsoft.com...
> > Hi guys,
> >           i've a strange problem that i define below
> > I've 2 tables in which from first table i want to take data from a
> > specific
> > filed and i want to update data of specific field of second table.
> >                    It's very easy to update data of one column but how
> > should i updata value for a series of column for example:
> >
> > update MBPRequestMain_TEMP
> > set CreatedBy =(select UserId from MobileBillUserDetails)
> > update MBPRequestMain_TEMP
> > set RequestFor =(select MobileOwnerName from MobileBillUserDetails)
> > update MBPRequestMain_TEMP
> > set ModifiedBy =(select Entrid from MobileBillUserDetails)
> >
> >               I don't want to run update statement seprately, which i was
> > doing above is there any way in which i can clubs this stmt. like wise
> >
> > update MBPRequestMain_TEMP
> > set CreatedBy,RequestFor,ModifiedBy   =(select
> > UserId,MobileOwnerName,Entrid
> > from MobileBillUserDetails)
> >
> >               I'm giving this query but it's giving error, plz make me
> > correct where i'm wrong.
> >
> >
> >
> >
> >
> >
>
>
>