Robert Francis
asked on
How do I convert this SQL SELECT into an UPDATE
I would like to update the Requested_Date on all the records returned by this SQL statement to 9/19/16.
How would I write that considering I am doing a JOIN.
Thanks in advance for your help.
SELECT dbo.Delivery.Promised_Date
FROM dbo.Job LEFT OUTER JOIN
dbo.Delivery ON dbo.Job.Job = dbo.Delivery.Job
WHERE (dbo.Job.Status <> 'template') AND (dbo.Job.Customer_PO = '114672') AND (dbo.Delivery.Requested_Date = CONVERT(DATETIME, '2016-09-15 00:00:00', 102))
How would I write that considering I am doing a JOIN.
Thanks in advance for your help.
ASKER
I added an addition to the WHERE clause to only change one record for the test (Line 9). Requested_Date was supposed to be Promised_Date but that was my bad (Line 8). Also Promised_Date is in the Delivery table, not Job table (Line 2). So here is the adjusted code:
I get the following error in SSMS:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "d.Promised_Date" could not be bound.
UPDATE j
SET d.Promised_Date = '20160919'
FROM dbo.Job j
LEFT JOIN dbo.Delivery d ON j.Job = d.Job
WHERE
j.Status <> 'template' AND
j.Customer_PO = '114672' AND
d.Promised_Date = CONVERT(DATETIME, '2016-09-15 00:00:00', 102) AND
j.Job = '105865'
I get the following error in SSMS:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "d.Promised_Date" could not be bound.
Explain what you're trying to do with the WHERE .. d.Promised_Date line. Is it a datetime or varchar? Guessing you intended to have a column as the second parameter and not a hard coded date? CONVERT in this manner converts to a varchar, so not sure why you have datetime there.
Check out SQL Server Date Styles (formats) using CONVERT() for some help with that expression.
Check out SQL Server Date Styles (formats) using CONVERT() for some help with that expression.
ASKER
Promised date is a datetime type. When I go into SSMS and create a view this is the SELECT statement it creates.
Yeah that seems odd, as you shouldn't be formatting values to shove into a datetime column. I'd work with that to make sure it's what you need, as it's not real clear to me what you're trying to pull off there.
ASKER
I'm confused as to why it's not real clear. I just want to mass change all the dates based on the results returned by my original query.
<correction>
Looking at the code block in my above comment, it appears the error was because of the SET statement with the d. table alias and not the WHERE clause. So correcting the table alias..
Looking at the code block in my above comment, it appears the error was because of the SET statement with the d. table alias and not the WHERE clause. So correcting the table alias..
UPDATE j
SET j.Promised_Date = '20160919'
FROM dbo.Job j
LEFT JOIN dbo.Delivery d ON j.Job = d.Job
WHERE
j.Status <> 'template' AND
j.Customer_PO = '114672' AND
d.Promised_Date = CONVERT(DATETIME, '2016-09-15 00:00:00', 102) AND
j.Job = '105865'
ASKER
Promised date is in the delivery table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked perfect. Thanks
Open in new window