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

General SQL Server Forums

New to SQL Server Programming

HELP URGETN

Author

Topic

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-20 : 13:39:48

WHY IS NOT WORKING?

declare @computersNV table (computerID int, computerName varchar(50), computerPrice varchar(30))
insert @computersNV
select 1, 'Rose', $1 union all
select 4, 'Manuel', $8

declare @computersCA table (computerID int, computerName varchar(50), computerPrice money)
insert @computersCA
select 1, 'Rose', $5.6 union all
select 4, 'Manuel', $8

/*
SELECT CA.COMPUTERPRICE FROM @COMPUTERSCA CA
WHERE NOT EXISTS (SELECT NV.COMPUTERPRICE FROM @COMPUTERSNV NV
WHERE NV.COMPUTERPRICE = CA.COMPUTERPRICE)
*/

update @computersNV
set NV.ComputerPrice = CA.ComputerPrice
from @computersNV NV
WHERE NOT EXISTS (SELECT ca.COMPUTERPRICE FROM @COMPUTERSCA CA
WHERE CA.COMPUTERPRICE = NV.COMPUTERPRICE)

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-20 : 13:45:56

FORGET IT I GOT IT WORKING

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-20 : 13:47:26

Don't know.

It would help if you urgently described what you are trying to do, the results you expect, and the error you are getting.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-20 : 13:57:58

What was your solution? Mine was this:


update NV set 
       NV.ComputerPrice = CA.ComputerPrice
from @computersNV NV
inner join @COMPUTERSCA CA 
       on ca.computerid = nv.computerid
WHERE isNull(CA.computerprice,-1) <> NV.computerprice

Be One with the Optimizer
TG

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-20 : 15:55:41

declare @computersNV table (computerID int, computerName varchar(50), computerPrice varchar(30))
insert @computersNV
select 1, 'Rose', $1 union all
select 4, 'John', $8

declare @computersCA table (computerID int, computerName varchar(50), computerPrice money)
insert @computersCA
select 1, 'Rose', $5.6 union all
select 4, 'Manuel', $8

SELECT NV.COMPUTERID, nv.computername nvName, ca.computername caName, NV.COMPUTERPRICE NVPC, CA.COMPUTERPRICE CAPC
FROM @COMPUTERSNV NV JOIN @COMPUTERSCA CA ON NV.COMPUTERID = CA.COMPUTERID
/*
UPDATE @computersNV
SET NV.ComputerPrice = CA.ComputerPrice,
NV.ComputerName = CA.ComputerName
FROM @computersNV NV
JOIN @computersCA CA
ON NV.COMPUTERID = CA.COMPUTERID
WHERE NOT EXISTS (SELECT CA.COMPUTERPRICE FROM @COMPUTERSCA CA
WHERE (CA.COMPUTERPRICE = NV.COMPUTERPRICE AND CA.COMPUTERname = NV.COMPUTERname))
*/

update NV set
NV.ComputerPrice = CA.ComputerPrice
from @computersNV NV
inner join @COMPUTERSCA CA
on ca.computerid = nv.computerid
WHERE isNull(CA.computerprice,-1) <> NV.computerprice

SELECT NV.COMPUTERID, nv.computername nvName, ca.computername caName, NV.COMPUTERPRICE NVPC, CA.COMPUTERPRICE CAPC
FROM @COMPUTERSNV NV JOIN @COMPUTERSCA CA ON NV.COMPUTERID = CA.COMPUTERID

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-20 : 16:21:48

Wow, your solution was exactly the same as TG’s!

CODO ERGO SUM

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-20 : 16:30:16

my is the one which is commented out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources