Using UPDATE with SELECT sub-query - confused!

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

Transact-SQL (2000)

Using UPDATE with SELECT sub-query - confused!

Author

Topic

nzmike
Starting Member

21 Posts

Posted - 2005-05-13 : 03:30:25

Hi,

I have a table called "works" which stores info about MP3 tracks in our database. I want to update the price of each track based on the number of seconds of each track - which is only stored in the MP3Info table. I tried the following query and it updated every row in Works rather than just the few which I know have a duration of less than 60 seconds... can anyone tell me why this doesn't work as I intended?

update works
set price = 0.99
where exists
(select * from works w, mp3info m
where w.workid = m.workid
and m.seconds < 60)

TIA for any help...

Mike

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-05-13 : 05:28:18

How about:


UPDATE w
SET w.Price = 0.99
FROM dbo.works AS w
JOIN mp3info AS m
ON w.workid = m.workd
WHERE m.seconds < 60

Mark

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2005-05-13 : 05:36:10

It could be that your data contains NULLS.
Try running your update statement as a SELECT, i.e
select * from works
where exists
(select * from works w, mp3info m
where w.workid = m.workid
and m.seconds < 60)
Does this return all rows?

Try this:
update works
set price = 0.99
where workid IN (select workid from mp3info where isnull(seconds, 0) < 60)

nzmike
Starting Member

21 Posts

Posted - 2005-05-13 : 20:24:46

Thanks folks... appreciate the feedback. Both methods worked just fine so now I can see where I went wrong... and my DB now has correct pricing (I had to it run a number of times for different song lengths and prices).

Cheers,

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources