update statement help - 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

update statement help

Author

Topic

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-19 : 18:32:23

how do u update rose 5.6 to 1?
thanks

declare @computersNV table (computerID int, computerName varchar(50), computerPrice varchar(30))
insert @computersNV
select 1, 'Rose', $1 union all
--select 2, 'Richard', $5 union all
--select 3, 'Mayo', $2 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 2, 'Richard', $7.5 union all
--select 3, 'Mayo', $4 union all
select 4, 'Manuel', $8

/*
Select CA.computerPrice, NV.computerPrice
from @computersCA CA
left join @computersNV NV
on CA.computerPrice = NV.computerPrice
where NV.computerPrice is null
*/

select * from @computersCA

update @computersCA
set CA.computerPrice = NV.computerPrice
from @computersCA CA
left join @computersNV NV
on CA.computerPrice = NV.computerPrice
where NV.computerPrice is null

select * from @computersCA

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-19 : 18:34:26

Welcome funketekun/gongxia649/escaleraroyal/lamujerdetuhermano10/etc...!!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-19 : 18:35:54

UPDATE @computersCA
SET computerPrice = 1
WHERE computerID = 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-19 : 18:38:49

seems like this forum is not taking the post seriously.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-19 : 18:39:22

quote:
Originally posted by lamujerdetuhermano10

seems like this forum is not taking the post seriously.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-19 : 18:39:30

Seems like you need to explain yourself better as I answered your question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-19 : 18:39:59

sodeep, please be respectful or you will suffer the same fate as funketekun.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-19 : 18:43:34

i just want to update the records that do not exists in the first table with the value from the second table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-19 : 18:51:55

I'm confused when you say "do not exist".

Could you show us what data should appear in both tables after the update runs?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-19 : 19:06:08

table1 table2
1 5.6
3 3

output
table1 table2
1 1
3 3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-19 : 19:28:42

I don't understand your output.

Show us exactly what these should return:
select * from @computersCA
select * from @computersNV

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-19 : 19:45:11

declare @computersNV table (computerID int, computerName varchar(50), computerPrice varchar(30))
insert @computersNV
select 1, 'Rose', $1 union all
select 2, 'Richard', $5 union all
select 3, 'Mayo', $2 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 2, 'Richard', $7.5 union all
select 3, 'Mayo', $4 union all
select 4, 'Manuel', $8

/*
Select CA.computerPrice, NV.computerPrice
from @computersCA CA
left join @computersNV NV
on CA.computerPrice = NV.computerPrice
where NV.computerPrice is null
*/

select * from @computersCA
select * from @computersNV

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-19 : 19:51:05

I guess you don't want help after all.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-19 : 19:58:26

computerid computername computerprice
1 Rose 1.00
2 Richard 5.00
3 Mayo 2.00
4 Manuel 8.00

computerid computername computerprice
1 Rose 5.60
2 Richard 7.50
3 Mayo 4.00
4 Manuel 8.00

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-19 : 19:59:13

im thinking this cannot be done on sql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-20 : 12:38:09

I'm thinking you don't know how to ask a question properly. This is very easy in SQL Server, but I can't waste my time anymore on this thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-22 : 03:58:30

quote:
Originally posted by lamujerdetuhermano10

how do u update rose 5.6 to 1?
thanks

declare @computersNV table (computerID int, computerName varchar(50), computerPrice varchar(30))
insert @computersNV
select 1, 'Rose', $1 union all
--select 2, 'Richard', $5 union all
--select 3, 'Mayo', $2 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 2, 'Richard', $7.5 union all
--select 3, 'Mayo', $4 union all
select 4, 'Manuel', $8

/*
Select CA.computerPrice, NV.computerPrice
from @computersCA CA
left join @computersNV NV
on CA.computerPrice = NV.computerPrice
where NV.computerPrice is null
*/

select * from @computersCA

update @computersCA
set CA.computerPrice = NV.computerPrice
from @computersCA CA
left join @computersNV NV
on CA.computerPrice = NV.computerPrice
where NV.computerPrice is null

select * from @computersCA

Your update should be

update @computersCA
set CA.computerPrice = NV.computerPrice
from @computersCA CA
join @computersNV NV
on CA.computerID = NV.computerID

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources