UPDATE FULL COLUMN - 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 FULL COLUMN

Author

Topic

redvino
Starting Member

2 Posts

Posted - 2010-10-12 : 06:36:20

Dear All,

PLease help me, i need to update whole column data from another table my query is:

UPDATE termination_SEP_09_10 A
SET A.P62FSITE =(SELECT [cOLUMN 10] FROM TERMINATION_AS_ON_12_10_10 B
WHERE A.ID = B.[cOLUMN 0])
where B.[column 8] ='C'
AND B.[COLUMN 9] ='Y'

This query thrown a error.

Vinoth Kumar.T.S
tsvinoth_19july@yahoo.co.in

kishore_pen
Starting Member

49 Posts

Posted - 2010-10-12 : 06:53:06

explain more about your error.

redvino
Starting Member

2 Posts

Posted - 2010-10-12 : 06:58:12

Dear kishore,
I got these two errors

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'A'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.

Vinoth Kumar.T.S
tsvinoth_19july@yahoo.co.in

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 09:17:16

[code]
UPDATE A
SET A.P62FSITE =(SELECT [cOLUMN 10] FROM TERMINATION_AS_ON_12_10_10 B WHERE A.ID = B.[cOLUMN 0])
from termination_SEP_09_10 A
where B.[column 8] ='C'
AND B.[COLUMN 9] ='Y'
[/code]

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-12 : 12:24:39

Try this until you can throw away your existing schema and do it right.

UPDATE Termination_Sep_09_10
SET p62fsite
=(SELECT column_10
FROM Termination_as_of_12_10_10 AS T
WHERE Termination_Sep_09_10.id = T.column_0)
WHERE B.column_8 = 'C'
AND B.column_9 = 'Y';

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'A'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.

This is the most awful code I have seen in years. Tables named with dates, like we did in the 1950's with magentic tapes file labels; but you are not consistent about the date format. This is bad design AND it is done wrong! A correct Relational design would make a termination date an atrribute of some entity in a table.

You name data elements by their ordinal positions with no regard for their meaning; and to really screw up things even more, you put spaces in the names so they cannot be used anywhere else. You have a magical, universal "id" on a table instead of a real key.

You assign alias names in alphabetic order to prevent easy mainteance. Think LOGICALLY and not PHYSICALLY. Tell me what it means and not where is it on a piece of paper being read left to right.

For the first error, do not use an alias in an UPDATE clause.

I do not understand why you got the second error; the syntax is fine. However, if the subquery is not a scalar, you will get a cardinality error.

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

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-12 : 12:44:23

You know Joe..you really are my Hero

I need to get some autographed books of yours

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources