SELECT nested in UPDATE not working 2

[fdarkness]

A little while ago, I had some help putting together an update query that selected the new data out of a table. It looks something like this:

UPDATE Assignments
SET EndDate = 
(SELECT EndDate
  FROM SF.dbo.Events
     WHERE SF.dbo.Events.EventNumber = Assignments.AssignmentNumber)
FROM Assignments, SF.dbo.Events

I tried to copy this to make a new similar query, but it doesn't seem to work... I let it run for about 8 minutes before I cancelled it out. There's not that much data that this would be running that long.

I need to update ClientID on the Assignment table. There's two other tables that are involved... The EventSpare table which contains the AssignmentNumber and ClientName and then the Client table which contains the ClientName and ClientID.

I formulated the following query, but it's not working.

UPDATE Assignments
SET ClientID = 
(SELECT ClientID
FROM SF_Clients
WHERE SF_Clients.ClientName = EventSpare.Client
	AND EventSpare.AssignmentNumber = Assignments.AssignmentNumber)
FROM Assignments, EventSpare

I know it's a bit convoluted and messy, but I don't have much control over the content of some tables. Any guidance would be appreciated!

 

[gmmastros]

First, make a backup of your database, then try this code.

UPDATE Assignments
SET    Assignments.EndDate = SF.dbo.Events.EndDate
From   Assignments
       Inner Join SF.dbo.Events
         On SF.dbo.Events.EventNumber.AssignmentNumber = SF.dbo.Events.EventNumber

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Yeah, I don't see any joins or interaction between the two tables Assignments and SF.dbo.Events in the top query, and Assignments and EventSpare in the second query, just thought I'd add that in

 

[fdarkness]

gmmastros: I'm not updating EndDate. The first query works fine... I used it to create the second query, which is the one that doesn't work.

monksnake: I tried putting in JOINs but that didn't work out at all. Where would I put them in?

 

[gmmastros]

My apologies. I modified the wrong query. Is this what you are looking for?

UPDATE Assignments
SET    Assignments.ClientID = SF_Clients.ClientId
From   Assignments
       Inner Join EventSpare
         Assignments.AssignmentNumber = EventSpare.AssignmentNumber 
       Inner Join SF_Clients
         On EventSpare.Client = SF_Clients.ClientName

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

I'm assuming you're updating every row in Assignments, so this is what I came up with: (be sure to backup your database first)

UPDATE Assignments
SET ClientID = 
(SELECT ClientID
FROM SF_Clients A
Inner Join EventSpare B
on A.ClientName = B.ClientName
WHERE B.AssignmentNumber = Assignments.AssignmentNumber)

This will set every ClientID in Assignments to one value, given that only one value is returned from the subquery, otherwise it will break. I'm not sure if this is what you want, but it's a start

 

[fdarkness]

Thanks both! I was able to put it together and get it to work!

 

[monksnake]

Cool

 

[rds80]

Is this the same

UPDATE A
SET A.ClientID = 
(SELECT SF.ClientID
FROM SF_Clients SF
Inner Join EventSpare ES
	on SF.ClientName = ES.ClientName
INNER JOIN Assignments A
	ON ES.AssignmentNumber = A.AssignmentNumber)