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.
| Author |
Topic |
|
MikeMacairan
Starting Member
3 Posts |
 Posted - 2010-11-30 : 15:27:44
|
| I have two tables and I'm trying to make a trigger on one, but my query doesn't work.(table names have been changed to protect the innocent)Department Tabledept_no dept_name1 Science2 Math3 EnglishStudent_Class Tablestudent_id dept_no dept_namet 3 Departmentu 3 Departmentv 3 Departmentw 2 Departmentx 3 Departmenty 1 Departmentz 3 DepartmentI need a query that will update the department name in Student_Class to match the data in the Department table, but when I try the following:update Student_Class set Student_Class.dept_name = Department.dept_name where Student_Class.dept_no = Department.dept_no I get an error that reads: "The multi-part identifier Department.dept_name could not be bound"I just want to make the dept_name field in Student_Class to match the dept_name in Department where the dept_no matches.Can someone plese help?Thanks. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-30 : 15:36:01
|
| You get the error because the Department table is not referenced anywhere. You need the UPDATE ... FROM style of update statement. Check Books Online (the SQL help file) for full details.p.s. This is a bad design. Department name has no business in the student table. It's a violation of 3rd normal form.--Gail ShawSQL Server MVP |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MikeMacairan
Starting Member
3 Posts |
Posted - 2010-11-30 : 15:51:37
|
| Thanks for the quick reply.I'm not surprised that it is a bad design. The tables are not really the ones in the database I am using, but I was trying to make a crude example for the sake of asking my question.I'm not a dba and am usually tasked only with querying the DB with very simple select queries to get answers, yet I have just been tasked with creating a trigger. My table (Student, as an example) gets data put into it but the dept_name will always have a generic value when the data arrives. I cannot control that; it's how the data comes to me.What I need to do is create a trigger that changes dept_name to some other value based on it's dept_number, so I created a table with two fields, dept_no and dept_name. I just want to update the dept_name in Student to match the one in Department where the dept_no is the same. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MikeMacairan
Starting Member
3 Posts |
Posted - 2010-11-30 : 16:01:53
|
| tkizer,Your example was perfect. I changed my query to match your design and it updated exactly as I needed it to.Thank you all for your quick and helpful responses. Mike |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Why doesn't my update statement work?