Interesting subquery problem - 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 2000 Forums

SQL Server Development (2000)

Interesting subquery problem

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2006-08-28 : 11:55:35

Hey everyone,

I've been racking my brain on this query problem for about 2 hours, so I've finally buckled. :-P

First off, here is the table:

SKUs
--------
sku_id varchar(50)
location_id uniqueidentifier
event_type varchar(50)
price money
slots_available int
price_type varchar(50)
owner varchar(50)
event_date smalldatetime
------------------------------------

What this table was going to do is hold specific data about an event on a certain date. Whenever someone registers for a specific event the slots_available column gets decremented by 1. There are multiple events per day, and two kinds of events--Early Bird (discounted rate) and Regular (no price discount). The problem I've run into is whoever was managing this system before I showed up didn't enter the Regular events, and now I need to find a way to update the Regular slots_available events to match the Early bird events.

The following rows will be identical between the Early bird and regular events:

Event_date,
event_type,
owner,
location_id

I need to create an Update query will be set the slots_available Regular events to equal the Early bird events.

This is the best I've been able to come up with (but my select sub-query returns all the rows, which won't do):

UPDATE  skus
SET slots_available = (SELECT slots_available FROM  skus 
WHERE (skus.event_date = skus.event_date) 
AND (skus.location_id = skus.location_id) 
AND (skus.price_type = 'Early'))

If anyone has any idea at all, you'd be my hero.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-28 : 12:18:13

Here is s quick shot in pseudo, i am always terrible at updates with joins in them....
Update s
Set s.slots_available=es.slots_available
FROM skus s
inner join skus es on
(s.event_date = es.event_date)
AND (s.location_id = es.location_id)
WHERE
(es.price_type = 'Early')

________________________________________________
"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks"

Linok
Starting Member

34 Posts

Posted - 2006-08-28 : 13:56:25

YAY!!!!!!!!

Thanks alot! My day just got a whole lot better!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources