×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Server - Update table and updated value

SQL Server - Update table and updated value

SQL Server - Update table and updated value

(OP)
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



RE: SQL Server - Update table and updated value

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

like this:

CODE

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

RE: SQL Server - Update table and updated value

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:

CODE

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/sql/relational-da...
OUTPUT Clause: https://docs.microsoft.com/en-US/sql/t-sql/queries...

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close