Update column from another join column ,,

Question

First it should be easy for the DB'es but its really giving me pain here i dont know why ,,

I have two tables , [Table1] named Applicationforms and [Table2] named WFInstance i need to update the FormSubmitDate in Table1 with the startedDate value in table 2 where the FormSubmitDate is null. I write the following query

UPDATE    ApplicationForm
SET              FormSubmitDate = wfi.StartDate
FROM         ApplicationForm app ,
                      WorkFlowInstances wfi

WHERE     app.FormSubmitDate is null and wfi.applicationID = app.ID

when i run the query the SQL regenerate the query to this one,,

UPDATE    ApplicationForm
SET              FormSubmitDate = wfi.StartDate
FROM         ApplicationForm AS app INNER JOIN
                      WorkFlowInstances AS wfi ON app.ID = wfi.ApplicationID CROSS JOIN
                      ApplicationForm
WHERE     (app.FormSubmitDate IS NULL)

I tried to write another statment using join ,,

UPDATE ApplicationForm
SET FormSubmitDate = wfi.StartDate
FROM ApplicationForm 
JOIN WorkFlowInstances wfi ON ApplicationForm.ID = wfi.ApplicationID 
WHERE  FormSubmitDate is null

and the SQL generate the same new syntax,,

now when i run the code , [all records] formSubmitDate fields filled with the first value of second table startDate. even if i have one record contains null value in table1

What i am missing here !?

does my suggestion help you?

idipous
– idipous

2013-04-09 07:40:11 +00:00
Commented Apr 9, 2013 at 7:40 — idipous
– idipous, Commented Apr 9, 2013 at 7:40

idipous · Accepted Answer · 2013-04-09 07:34:20Z

4

I think this does what you want. Try it and let us know.

UPDATE tabl3
SET formsubmitdate=(SELECT startdate
  FROM table4
  WHERE table4.id=tabl3.id AND tabl3.formsubmitdate is null);

Another way to write the same thing would be

UPDATE tabl3
SET formsubmitdate= startdate
FROM table4 where tabl3.id = table4.id and formsubmitdate is null

edited Apr 9, 2013 at 7:34

answered Apr 9, 2013 at 7:20

idipous

2,9103 gold badges38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Moe Over a year ago

finally ,, the second query is worked ,, can i ask you why you set from table 4 , since we need to update in table 3 ?! i was trying the same query but with [ from table 3],,, I am not very good in DB , but as fare as i know that the join returns the mach record right ?!

idipous Over a year ago

It is startdate from table4 that's why it works. The join returns all the rows that satisfy the on condition. What I have written is different conceptually to the join.

Collectives™ on Stack Overflow

Update column from another join column ,,

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related