SQL Server - Update table and updated value 1

[Qamar Zaman]

Hi Genius,

i need help, my code work successfully, but i need updated value in one sentence code.

My code is :-
qcheck=SQLEXEC(softDB,"UPDATE stock SET qty = qty + 1 ")
OUTPUT = (qty + 1) (I need OUTPUT value, mean in need update table and get updated value in one sentence code)

Thanks,
Qamar

 

[Olaf Doschke]

1. You don't want to increase the qty of all stock by 1. Add a WHERE clause like [tt]WHERE productid = 42[/tt]
2. You can execute a batch of an UPDATE followed by a SELECT

like this:

softDB = SQLStringConnect("driver=sql server;server=...")

Local lcSQL, lnProductID
lnProductID = 42

Text To lcSQL NoShow 
Update stock set qty=qty+1 WHERE productid = ?m.lnProductID
Select qty from stock WHERE productid = ?m.lnProductID
EndText

qcheck = SQLExec(softDB,lcSQL,"crsQty")
If qcheck<0
   AError(laError)
   * error handling
   Set Step On 
Else
  ? 'qty ia', crsQty.qty   
EndIf
   
SQLDisconnect(softDB)

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

You can also use an OUTPUT clause of T-SQL queries. That's possible for inserts, updates, deletes and merges. But in principle it's still the same, you can't just output with no target table for it, so it's still a command batch you need to execute:

Declare @output as table (qzy int);

Update stock set qty=qty+1 output inserted.qty into @output WHERE productid = 42 

Select * from @output

The only difference is, that you don't address the stock table twice, you address the table variable instead.

And, of course, in unspecific UPDATEs, where you don't know which record ids are updated, this is the only way to get exactly the updated values of the updated rows, but then you'd also better include the primary key field of the table.

Read up on triggers and the inserted and deleted system table variables and of course the output clause in the T-SQL documentation.

Use the inserted and deleted Tables:

https://docs.microsoft.com/en-us/sq...rted-and-deleted-tables?view=sql-server-ver15

OUTPUT Clause:

https://docs.microsoft.com/en-US/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name