update tables and replace values

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

General SQL Server Forums

New to SQL Server Programming

update tables and replace values

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2011-06-21 : 10:20:17

Hello,

I have two tables as below.

I tried to create a query to replace the values of Student.Dept_name from Dept.Dept_Name where there is same Dept_ID.

Here is my query:
UPDATE Student
SET Student.Dept_Name = Dept.Dept_Name
WHERE Student.Dept_ID = Dept.Dept_ID

Dept
Dept_ID Dept_Name
1 A1
2 B1
3 C1
4 A1
5 C1
6 B1

Student
ID Dept_ID Dept_Name
1 2 A
2 3 A
3 2 A
4 1 A
5 3 A

As a result, Student table should have this data after I run the query:
ID Dept_ID Dept_Name
1 2 B1
2 3 C1
3 2 B1
4 1 A1
5 3 C1

I got this error:
The column prefix 'Dept' does not match with a table name or alias name used in the query.

can you help?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 10:29:16

You have to include the Dept table, for example:

UPDATE Student
SET Student.Dept_Name = Dept.Dept_Name
FROM Student INNER JOIN Dept
On Student.Dept_ID = Dept.Dept_ID

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-21 : 10:29:27

UPDATE Student
SET Dept_Name = Dept.Dept_Name
from Student
join Dept
on Student.Dept_ID = Dept.Dept_ID

Yours would almost work with a from statement but I prefer this syntax.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2011-06-21 : 13:39:32

It worked perfectly.

Big thanks.

There is no stupid question.
Asian Girls at Free Online Dating

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-21 : 17:20:05

>> I have two tables as below. <<

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

Do you really have only one department and one student. Likey0ou said when you used singular names? Your whole data model is wrong; a department is not an attribute of a student; a student and a department have a relationship since they are distinct, separate entities. The idea of a magical generic “id” is absurd.

CREATE TABLE Departments
(dept_id INTEGER NOT NULL PRIMARY KEY,
dept_name VARCHAR(20) NOT NULL,
..);

CREATE TABLE Students
(student_id INTEGER NOT NULL PRIMARY KEY,
student_name dept_name VARCHAR(20) NOT NULL,
..);

Now we have to guess from your lack of specs if this is 1:1, 1:m or a n:m relationship. Read these DDLs until you understand them

CREATE TABLE Enrollment
(dept_id INTEGER NOT NULL UNIQUE
REFERENCES Departments(dept_id),
student_id INTEGER NOT NULL
REFERENCES Students(student_id));

CREATE TABLE Enrollment
(dept_id INTEGER NOT NULL
REFERENCES Departments(dept_id),
student_id INTEGER NOT NULL UNIQUE
REFERENCES Students(student_id));

CREATE TABLE Enrollment
(dept_id INTEGER NOT NULL
REFERENCES Departments(dept_id),
student_id INTEGER NOT NULL
REFERENCES Students(student_id));

>> I tried to create a query to replace the values of Students.dept_name [sic] from Departments.dept_name where there is same dept_id. <<

An UPDATE is a statement and not a query. You seem to need more help tha nyou can get on the Internet; might want to get some basic books.

If you want to see the names, then do a simple VIEW with three tables. Left as an exercise for the student :)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources