please help me with this query

[uniquegodwin]

Hello,
Can someone help me with this query...I want to update the leadactivities column based on a value from another table.Please help.

UPDATE    tblLeadActivities SET f_activityName =  (SELECT     f_activityName
  FROM          tblStandardActivities) WHERE     (f_leadId = '0602HOUS3')

I am using SQL EXPRESS 2005 with Asp.Net 2.0..the latest one.Please help me,Thanks so much muchhhhh.

[Hack]

I believe you would need to preface your field names with their tablenames.

[brucevde]

You must ensure the sub-query returns only one row, so add a where clause. If there are still multiple records use the Top clause.

UPDATE tblLeadActivities 
SET f_activityName =  
            (SELECT f_activityName FROM tblStandardActivities Where ...) 
WHERE (f_leadId = '0602HOUS3')

[dee-u]

This seem similar...

[szlamany]

In MS SQL - UPDATES can use JOIN'd tables and alias's as long as you alter the syntax slightly...

Update tblLeadActivities Set f_activityname=ts.f_activityname
   From tblLeadActivities ta
   Left Join tblStandardActivities ts on ts.keycol=ta.keycol
   Where ta.checkcol={whatever} or ts.checkcol={whatever}

The BOLD part looks just like a standard SELECT/FROM/JOIN - all the same possibilties...

Because of that altered syntax, you can use the alias ts. on the right-side of the equals in the UPDATE/SET list.

Hope this helps...

[uniquegodwin]

Hello,
Thanks for all your responses
But,the solution for me was this...

declare
	@LeadID		varchar(15),
	@rec_count	int,
	@idx		int

select	@LeadID		= '001'
select	@rec_count = count(*)
	from	tblLeadActivities
	where	f_leadId = @LeadID

select	@idx = 1
set rowcount 1	
while @idx <= @rec_count
begin
	update	u
		set	u.f_activityName = (select top 1 x.f_activityName 
						from 	tblStandardActivities x
						where	not exists (select * from tblLeadActivities y
									where	y.f_activityName 	is not null
									and	y.f_activityName 	= x.f_activityName
									and	y.f_leadId 		= u.f_leadId)
						order by newid()
					    )
	from	tblLeadActivities u
	where	u.f_leadId		= @LeadID
	and	u.f_activityName	is null
	select	@idx = @idx + 1
end
set rowcount 0

select * from tblLeadActivities

[uniquegodwin]

Actually that code copies the contents of one column in a table to another table...based on certain conditions.
It was given to me by a friend.
But,Can someone help me in mdifying that code to copy in the same order of the source table?
Thanks

[Hack]

So, is this problem resolved?

If so, could you please pull down the Thread Tools menu and click the Mark Thread Resolved button. That will let everyone know that you have your answer.

Thank you.

[uniquegodwin]

Yes I will do that soon sir but please help me with that one last problem related to the same issue.
Thanks

[szlamany]

Did you see my post (#5) with the enhanced syntax for an UPDATE statement with JOIN possibilities?

[uniquegodwin]

Yes I did but the problem is I dont have a key linking both tables...

[szlamany]

You do not need a key to link - you can link on whatever you want...

Isn't this your where clause?

where	y.f_activityName is not null
and	y.f_activityName = x.f_activityName
and	y.f_leadId = u.f_leadId

That could easily be changed to an ON clause in a join.

Regardless of whether you use a sub-query in the UPDATE or a JOIN - you still need to "related" the rows together on some condition.

If you can show me some real data from these two tables I can work up a better example.

[uniquegodwin]

Dear szlamany,
The whole thing I actually wanted in quite complicated...It was probably my fault of not explaining the question properly.Its kind of hard to explain it through text here,but I will try.
I have 3 tables.
tblLeads
tblLeadActivities
tblStandardActivities.

tblLeads and tblLeadactivities have a key called serial number.
tblLeadactivities and tblStandardactivities have no key.

Now,everytime I enter a new record into the tblLeadactivities,It is entered as 7 copies leaving the activityname field as null.
Then,I need to select the list of activity names from the tblStandardActivities table and fill them up each for each record in the activitiyname column the tblLeadActivities table.

I hope you got the picture now.

Thanks for your time again