update query - SQL Server Forums

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

Transact-SQL (2005)

update query

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-02-23 : 04:11:50

Hi,

I am trying to update the table ‘contact’ and in the field ‘temp’ put a value of ‘on course’ with the results of this select query below. The link between the two tables are in the ‘contact’ table ‘edrs’ and in the ‘wce_course_delegate_link_2’ table ‘edrs_no’


SELECT     distinct edrs_no  FROM         wce_course_delegate_link_2 AS d INNER JOIN wce_contact AS c ON d.edrs_no = c.edrs where (d.end_date is null) and edrs_no <> ''

I have tried several variations of update query and cannot seem to get the results I need.

So this query below is logically what I want but not logical to SQL to give me the results i need. I’m thinking SQL is more logical than me so I could use some advice please :-) Thanks in advance.


Update contact set temp = ‘on course’ where(SELECT     distinct edrs_no
	FROM         wce_course_delegate_link_2 AS d 
	  INNER JOIN wce_contact AS c ON d.edrs_no = c.edrs 
where (d.end_date is null) and edrs_no <> '')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-23 : 04:40:36

You should give table structure, example data and wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2011-02-23 : 05:04:29

Try this



Update contact set temp = ‘on course’ from  wce_course_delegate_link_2 AS d 
	  INNER JOIN wce_contact AS c ON d.edrs_no = c.edrs 
where (d.end_date is null) and edrs_no <> ''

PBUH

mshsilver
Posting Yak Master

112 Posts

Posted - 2011-02-23 : 06:39:55

Great, i think that got it. One thing i am trying to figure out and something that has come to light. There will be multiple rows in the wce_course_delegaate_linkt_2 table that have the same id but on one row in the contact table. Will taht impact this query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources