Updated - Declare a value and set with different results
sql set variable
sql declare and set variable
sql set variable from select
assign value to variable in sql select statement
sql set multiple variables from select
sql display variable
declare variable in sql function
Hi I am not sure if this is possible the way I am going about this problem, but if you have an solution or alternative way to overcome this, it would be greatly appreciated.
To explain, I have a list of tracking numbers and I want to declare the "ShipDate" as the "AddedDate" and after declare the "DueDeliveryDate" as "ShipDate"+days depending what day it was shipped.
The issue here is it will update all the tracking numbers with the same date which is declared first for both "ShipDate" and "DueDeliveryDate".
See SQL code:
begin tran Declare @ShipDate varchar(max) = (Select cast(AddedDate as date) from (select distinct a.TrackingNumber, b.AddedDate from ConTransaction a inner join Consignment b on a.TrackingNumber = b.TrackingNumber where a.TrackingNumber in ( Select TrackingNumber from Consignment C WITH (NOLOCK) Where ShipDate > DateAdd(d, 7, Convert(smalldatetime,Convert(Varchar(10),getdate(),126))) AND [Status] = 0 ))part01) Declare @DueDeliveryDate datetime Begin if datename(weekday,@ShipDate) = 'Friday' set @DueDeliveryDate = dateadd(day,3,@ShipDate) if datename(weekday,@ShipDate) = 'Saturday' set @DueDeliveryDate = dateadd(day,2,@ShipDate) else set @DueDeliveryDate = dateadd(day,1,@ShipDate) Update CN Set ShipDate = @ShipDate ,DueDeliveryDate = @DueDeliveryDate ,UpdatedBy = 'IT ADMIN' -- Select * FROM dbo.Consignment CN Where cn.TrackingNumber in ( select distinct a.TrackingNumber from ConTransaction a inner join Consignment b on a.TrackingNumber = b.TrackingNumber where a.TrackingNumber in ( Select TrackingNumber from Consignment C WITH (NOLOCK) Where ShipDate > DateAdd(d, 7,Convert(smalldatetime,Convert(Varchar(10),getdate(),126))) and [Status] = 0 and TrackingNumber in ( '65560460605' , '50454646064') )) end --rollback tran
Here is a snippet of the table:
TrackingNumber | ShipDate | UpdatedBy | AddedDate | Status | DueDeliveryDate 65560460605 | 2018-08-10 00:00:00 | NULL | 2018-08-06 00:00:00 | 0 | 2018-09-09 00:00:00 50454646064 | 2018-08-12 00:00:00 | NULL | 2018-08-10 00:00:00 | 0 | 2018-09-10 00:00:00
Begin Update CN Set ShipDate = @ShipDate ,DueDeliveryDate = case when datename(weekday,@ShipDate) = 'Friday' then dateadd(day,3,@ShipDate) when datename(weekday,@ShipDate) = 'Saturday' then dateadd(day,2,@ShipDate) else dateadd(day,1,@ShipDate) end ,UpdatedBy = 'IT ADMIN' ,UpdatedDate = getdate() -- Select * FROM dbo.Consignment CN Where cn.TrackingNumber in ( **List of tracking number** )
When to use SET vs SELECT when assigning values to variables in , SET and SELECT may be used to assign values to variables through T-SQL. By: Atif Shehzad | Updated: 2009-11-25 | Comments (13) | Related: More > T-SQL To further clarify the concept please run script # 1 in two separate parts to see the results Populate by multiple rows through SET DECLARE @Var2ForSet Unlike SET, if the query results in multiple rows then the variable value is set to the value of the last row. If the query returns zero rows, then the variable is set to EMPTY, i.e., NULL. Query 1: The query returns one row. DECLARE @COURSE_NAME VARCHAR (10) SELECT @COURSE_NAME = Tutorial_name from Guru99 where Tutorial_ID = 3 PRINT @COURSE_NAME
I would rewrite it with
case expression :
set @DueDeliveryDate = dateadd(day, (case datename(weekday,@ShipDate) when 'Friday' then 3 when 'Saturday' then 2 else 1 end), @ShipDate ) update cm set ShipDate = @ShipDate, DueDeliveryDate = @DueDeliveryDate, UpdatedBy = 'IT ADMIN', UpdatedDate = getdate() from dbo.Consignment cn where cn.TrackingNumber in ( **List of tracking number** );
How to assign a select result to a variable?, DECLARE @tmp_key int DECLARE @get_invckey cursor SET @get_invckey @tmp_key UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey What happens if the following select results in several posts? In this article, I will provide a set of examples to showcase the use of OUTPUT clause in capturing the results of the updated rows into a table variable for the UPDATE statements. Getting Started
See solution below:
UPDATE a SET ShipDate = a.AddedDate ,DueDeliveryDate = CASE WHEN datename(weekday,a.AddedDate) = 'Friday' THEN dateadd(day,3,a.AddedDate) WHEN datename(weekday,a.AddedDate) = 'Saturday' THEN dateadd(day,3,a.AddedDate) ELSE dateadd(day,1,a.AddedDate) END ,UpdatedBy = 'IT ADMIN' ,UpdatedDate = getdate() -- Select * FROM dbo.Consignment a Where TrackingNumber in ( SELECT DISTINCT a.TrackingNumber FROM ConTransaction a INNER JOIN Consignment b ON a.TrackingNumber = b.TrackingNumber WHERE a.TrackingNumber in (SELECT TrackingNumber FROM Consignment C WITH (NOLOCK) WHERE ShipDate > DateAdd(d, 7, Convert(smalldatetime,Convert(Varchar(10),getdate(),126))) AND [Status] = 0 AND TrackingNumber IN ( 65560460605, 50454646064 ) ) )
*So I update the "ShipDate" with the "AddedDate" then depending on the day I updated the "DueDeliveryDate" using the "AddedDate" *
If any can suggest a different title it make this question stand out a bit better feel free to let me know and I will update it.
Thanks for your help!
SQL Server Variable: Declare, Set, Select, Global,Local [TSQL , Types of Variable: Local, Global; How to DECLARE a variable Unlike SET, if the query results in multiple rows then the variable value is set to -- Declare the variables to receive the output value and return code -- of the procedure. DECLARE @SalesYTDForSalesPerson money, @ret_code int; -- Execute the procedure with a title_id value -- and save the output value and return code in variables. EXECUTE @ret_code = Sales.usp_GetSalesYTD N'Blythe',
This article explores the SQL variables using SET and Select SQL , SQL Server provides us with two methods in T-SQL to assign a value to a variable that is previously defined using the DECLARE statement, The results of the previous two queries will be displayed in the Messages tab as shown below: In the below example, each variable requires a separate SET In an update driven by a join, a variable that is assigned a value in the right hand side of the update statement uses columns from the table that is not being updated. The result value depends on the join order chosen for the update and the number of rows that qualify from the joined table.
SET @local_variable (Transact-SQL), If the target cursor variable previously referenced a different cursor, that previous Specifies that the SET statement contains a declaration of a cursor. Updates of key values from outside the cursor are similar to a delete of the old to the rows in its result set as the cursor owner scrolls around the cursor. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.
DECLARE @local_variable (Transact-SQL), Transact-SQL reference for using DECLARE to define local variables DECLARE statement and are assigned values by using either a SET or Cursor variables can be declared with this statement and used with other cursor-related that return the values in @MyTableVar and the results of the update This means that the SET clause of the UPDATE statement is evaluated once for every row in the updated table. With the SELECT statement, you can’t assign variables and return an output in the same statement. UPDATE, however, does allow you to update a table column and assign values to variables in the same statement,
- The reason is because you're using a single value for @DueDeliveryDate and applying it in one single set operation (ie- the UPDATE). Can you post some sample data? The logic should be done within the UPDATE statement for it to work on a record by record basis.
- Well you calculate the values of the variables once and then update the table with this values. No wonder all records affected share the same values. Somehow the dates should be relative to a value stored in the table. But unfortunately your question doesn't explain which value that should be. Try to edit the question and explain that.
- So the "cumbersome" part where you determine the
@ShipDateis probably the key to understanding this. At present you have three answers, but they are all pretty much useless, as all they do is rewrite your bad logic into other forms of the SAME bad logic. We need to see how you calculate "AddedDate" as "ShipDate" as this will probably be the crux of the problem.
- I have updated the question
- I have solved it I will update the the question with the answer. I was making it more difficult than it needed to be. Thanks for all your help. Once you see the script it will be a lot clear what I needed to do.
- Thanks Cetin, you pointed me in the right direction I will post the correct solution and you can see what I was trying to accomplish.