Update sql table

31 Aug 2006 6:33 PM

ntuyen01

Hi All,

Please help.

Example :

I have a table "ABC" --- -- Only one row for the ID
ID = 5, ColX, ColY
ID = 6 ColX, ColY

I have another table 'XYZ --- Many row for the same ID
ID = 6 ColX = 'E'
ID = 6 ColY = 'E'
ID = 6 ColZ = ' '
ID = 5 ColX = '01'
ID = 5 ColY = '02'
ID = 5 ColZ = '03'

I want to update the table 'ABC' column ColX, ColY from table 'XYZ'
where ID = 5.

How do I do it.

Thanks in advance.
Ted Lee

31 Aug 2006 6:46 PM

David Portas

ntuye***@yahoo.com wrote:
Show quote

> Hi All,
>
> Please help.
>
> Example :
>
> I have a table "ABC" --- -- Only one row for the ID
> ID = 5, ColX, ColY
> ID = 6 ColX, ColY
>
> I have another table 'XYZ --- Many row for the same ID
> ID = 6 ColX = 'E'
> ID = 6 ColY = 'E'
> ID = 6 ColZ = ' '
> ID = 5 ColX = '01'
> ID = 5 ColY = '02'
> ID = 5 ColZ = '03'
>
> I want to update the table 'ABC' column ColX, ColY from table 'XYZ'
> where ID = 5.
>
> How do I do it.
>
> Thanks in advance.
> Ted Lee

Please post DDL, sample data and expected results. Without that
information we can only guess what the keys and constraints are.

http://www.aspfaq.com/etiquette.asp?id=5006

You say you want to update ColX and ColY but you also say you have many
rows to go into one. Does that mean you want the values from just one
row? In that case, which row is it? Or do you want total values?
Averages? Something else?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

31 Aug 2006 7:31 PM

maneoman

With only the info you gave, try this

update abc set a.colx = x.colx, a.coly = x.coly
from abc a, xyz x where
a.id = x.id and x.id = 5