May 27, 2018 at 9:13 am
I use the query below to identify any records that have a match between the table and view. The query works fine but how can I create an update query update the matching records in dbo.VAC_tbl_Requests_NIL?
I would like to set the fields:
CancelID = 1
CanceledDateTime = Now(), or the SQL equiviilent
CanceledBy = 'Auto Routine'
but only for those records that have a matching NIL_ID in the query below.
SELECT dbo.VAC_tbl_Requests_NIL.CancelID, dbo.VAC_tbl_Requests_NIL.CanceledDateTime, dbo.VAC_tbl_Requests_NIL.CanceledBy
FROM dbo.VAC_tbl_Requests_NIL INNER JOIN
dbo.VAC_View_Requests_ToCancel_DayOff_NIL ON dbo.VAC_tbl_Requests_NIL.AbsenceID = dbo.VAC_View_Requests_ToCancel_DayOff_NIL.AbsenceID
WHERE (dbo.VAC_tbl_Requests_NIL.CancelID IS NULL)
May 27, 2018 at 4:04 pm
ordnance1 - Sunday, May 27, 2018 9:13 AMI use the query below to identify any records that have a match between the table and view. The query works fine but how can I create an update query update the matching records in dbo.VAC_tbl_Requests_NIL?I would like to set the fields:
CancelID = 1
CanceledDateTime = Now(), or the SQL equiviilent
CanceledBy = 'Auto Routine'but only for those records that have a matching NIL_ID in the query below.
SELECT dbo.VAC_tbl_Requests_NIL.CancelID, dbo.VAC_tbl_Requests_NIL.CanceledDateTime, dbo.VAC_tbl_Requests_NIL.CanceledBy
FROM dbo.VAC_tbl_Requests_NIL INNER JOIN
dbo.VAC_View_Requests_ToCancel_DayOff_NIL ON dbo.VAC_tbl_Requests_NIL.AbsenceID = dbo.VAC_View_Requests_ToCancel_DayOff_NIL.AbsenceID
WHERE (dbo.VAC_tbl_Requests_NIL.CancelID IS NULL)
Something like this should do it (and notice how aliasing table names makes things more readable):
UPDATE req
SET CancelID = 1,
CanceledDateTime = GETDATE(),
CanceledBy = 'Auto Routine'
FROM dbo.VAC_tbl_Requests_NIL req
JOIN dbo.VAC_View_Requests_ToCancel_DayOff_NIL dayoff
ON req.AbsenceID = dayoff.AbsenceID
WHERE req.CancelID IS NULL;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 27, 2018 at 6:08 pm
Thank you so much for that help
May 27, 2018 at 7:33 pm
>> I use the query below to _identify any records [sic: rows are records] that have a match between the table and view. The query works fine but how can I create an update query [sic: this is a statement, not a query] update the matching records [sic] in VAC_Requests_NIL?
I would like to set the fields [sic:columns are not fields; read the ANSI/ISO standards for what a field means in this language]<<
If you followed ISO 11179 standards, your column names would be more like this. The postfix is called an atttribute property. Putting metadata into the names of attributes is also a big no-no; read the metadata committee papers for painful details. In particular, the use of "tbl" the data element name is so bad we call it a Tibble and make fun of people who do it. Likewise, putting "vw" or "view" interview name is called a Volkswagen and is also subject to r_idicule.
Isn't the use of "now" an old Access convention or something? SET cancellation_timestamp = CURRENT_TIMESTAMP;
How can an identifier, such as your cancellation_id, be a null? This violates a fundamental principle of logic called the law of identity.
If you follow forum netiquette, and post some DDL, perhaps we can actually give you some help.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 28, 2018 at 5:58 am
I certainly apologizes for my ineptitude.
I am about as far from an SQL programmer as you can get, I am in fact not a programmer at all. I am a para-transit dispatcher, at least for the next 14 days, when I retire. I am someone that sees a problem and looks for solutions. I then look to groups like this to help fill the considerable gaps in my knowledge. This is the culmination of a very long project that started out as an Access solution and has moved to SQL Server at the request of our IT department. When I retire they become owners of the project and they live in an SQL word not an Access World.
I do take constructive criticism to heart and will attempt to provide better information in the future. I posted my SQL and Phil was able to post a solution. Again acknowledging my ignorance but how do you post DDL?
May 28, 2018 at 6:19 am
May 28, 2018 at 6:23 am
Mr Celko
I researching some of your suggestions, I have stumbled upon your series Stairway to Database design. As I prepare to retire I have a home weather station database I want to develop, just to keep my mind active, and will review your series before I start.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply