updating temp table from selects

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 2000 Forums

SQL Server Development (2000)

updating temp table from selects

Author

Topic

havey
Starting Member

16 Posts

Posted - 2008-02-23 : 21:08:43

Hi,

i'm trying to update a temp table "@a" columns GoalID & Comments, with values from another database. @a temp table has column GoalType, hence i try to use it in the select statement below like so:
[ElectronicV1].[dbo].[GoalTypes].[GoalType] = GoalType

I'm getting incorrect syntax near GoalID, this is what i have.. thanks

update @a set GoalID =

(SELECT [GoalTypeID] FROM [ElectronicV1].[dbo].[GoalTypes] WHERE
[ElectronicV1].[dbo].[GoalTypes].[GoalType] = GoalType),

Comments=(SELECT [Comments] FROM [ElectronicV1].[dbo].[GoalTypes] WHERE [ElectronicV1].[dbo].[HealthGoalTypes] .[GoalType] = GoalType)

WHERE GoalID is null

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-23 : 22:25:02

@a wouldn't be a temp table...

But..given your sample syntax

You don't need the 4 part naming in the WHERE clause...
You also reference a DIFFERENT table in your 2nd statement's where clause

quote:

Comments=(SELECT [Comments] FROM [ElectronicV1].[dbo].[GoalTypes] WHERE [ElectronicV1].[dbo].[HealthGoalTypes] .[GoalType] = GoalType)


Update @a
SET GoalID = g.[GoalTypeID]
    Comments = h.[Comments]
FROM [ElectronicV1].[dbo].[GoalTypes] g  inner join [ElectronicV1].[dbo].[HealthGoalTypes] h 
    on g.[GoalType] = h.[GoalType]
 WHERE  g,[GoalType] = GoalType

I am not sure what the = GoalType is referring to...is that a variable or something?

Honestly, it is becoming less clear as i re-read this what you are actually trying to do.

Poor planning on your part does not constitute an emergency on my part.

havey
Starting Member

16 Posts

Posted - 2008-02-23 : 22:32:04

dataguru your awsome, you gave me the start to a successful end, cheers:

Update @a
SET GoalID = g.[GoalTypeID],
Comments = g.[Comments]
FROM [ElectronicV1].[dbo].[GoalTypes] g
inner join @a h
on g.[GoalType] = h.[GoalType]
WHERE g.[GoalType] = h.GoalType

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-23 : 22:39:37

quote:
Originally posted by havey

dataguru your awsome, you gave me the start to a successful end, cheers:

Update @a
SET GoalID = g.[GoalTypeID],
Comments = g.[Comments]
FROM [ElectronicV1].[dbo].[GoalTypes] g
inner join @a h
on g.[GoalType] = h.[GoalType]
WHERE g.[GoalType] = h.GoalType

You don;t need the where clause. The INNER JOIN will limit the update to only matching records on the joining column already.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources