Help Needed With Update Statement

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 Needed With Update Statement

Author

Topic

boybles
Starting Member

23 Posts

Posted - 2009-05-29 : 23:51:18

I want to write an update statement so it updates the appropriate RequestID values in the Schedule table when joined between Request.PlanID and Schedule.PlanID as
shown in "Desired Result". Please help!!
Thanks,
Tony

[Tables]

Request
--------
RequestID (int)
PlanID (int)
Name (varchar)

Schedule
---------
ScheduleID (int)
PlanID (int)
RequestID (int)
StartTime (datetime)

[Data]

Request
--------------
RequestID|PlanID|Name
--------------
10|1|EventXXX
11| |EventZZZ
12|2|EventYYY
13| |EventAAA
14|3|EventCCC

Schedule
------------------------------
ScheduleID|PlanID|RequestID|StartTime
------------------------------
1|1| |12:00
2| | |9:30
3|2| |5:30
4|3| |8:30
5| | |10:30

[Desired Result]

Once the update statement is executed, the updated Schedule table should look like this:

Schedule
------------------------------
ScheduleID|PlanID|RequestID|StartTime
------------------------------
1|1|10|12:00
2| | |9:30
3|2|12|5:30
4|3|14|8:30
5| | |10:30

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-30 : 00:52:05

this?

Update a
Set a.RequestID = b.RequestID
from Schedule a inner join Request b
on a.PlanID = b.PlanID

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-30 : 01:02:33

Am I right in thinking that each plan is related to exactly one request and one schedule? If not then the update you want to do is not possible because there might be multiple RequestIDs for a given plan and you can only put one RequestID in the column in the Schedule table.

However, if my assumption is correct then you don't need to put the RequestID in the Schedule table at all because you can just join the Schedule table to the Plan table and the Request table whenever you need to know what the RequestID is for a given Schedule.

A join is the way you'd do the update you asked about if you decide to follow through with that design (I'd recommend against it though)
UPDATE Schedule
SET Schedule.RequestID = Request.RequestID
FROM Schedule
INNER JOIN Request ON Request.PlanID = Schedule.PlanID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources