Select from a table and update same dataset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select from a table and update same dataset

Author

Topic

Siopold
Starting Member

9 Posts

Posted - 2009-05-22 : 20:22:12

Hi,

I am relatively new to SQL but am a programmer. I hope someone can help here.

What I want to do is select some data and if a record is found
update a status flag to say that the record has been processed, relatively straightforward.

My Select is below.

I know that I can do an 'update where exists' around the select below. However when I do that it updates all records on the table not the one record returned from the select.

Ideally I would like the update to be part of the where. Having read a bit about this it looks like I may have to restructure the statement or write a second statement with the update in which seems pretty inefficient.

Hope I have explained this clearly, my first question here

Select
various fields
from tablea joined to two other tables

-- Looking to retrieve specific record or previous

where
(a.date in (Select Top 1 a.date
from tableb
where a.date <= b.date2
order by a.date desc))

group by
various fields

order by b.date2

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-23 : 01:10:48

Just use a subquery in WHERE clause as you're thinking to. Essentially it runs before the UPDATE query, and returns a value that is then used by the UPDATE query, so something like:

UPDATE tablea
SET tablea.col = ...
WHERE tablea.col = (SELECT col FROM ...)

You can also use a JOIN in an UPDATE, I'm not certain from your description what you're trying to do, but you can probably also do something like this - note that an UPDATE always updates only the table specified by the UPDATE statement, none of the other tables in the JOIN are updated, they are just used to locate the rows in the UPDATE table and/or provide values to use to update the UPDATE table:

UPDATE
SET various fields = various other fields
from tablea joined to two other tables

-- Looking to retrieve specific record or previous

where
(a.date in (Select Top 1 a.date
from tableb
where a.date <= b.date2
order by a.date desc))

group by
various fields

order by b.date2

Siopold
Starting Member

9 Posts

Posted - 2009-05-23 : 10:04:46

First of all thanks very much for your response. I have written the update statement same as select i.e. joining the fields and repeating the 'select top 1..' code and that works a treat. I can simply run the update after I run the select as a second statement.

A quick explanation of what it is I am doing.
I have an invoice table which contains an Invoice Date. I am selecting all invoices from this table.
For each invoice found I look up the Currency Exchange Rate table to pick up the correct exchange rate.
So, if an invoice date matches a currency exchange rate date then return that currency exchange rate and apply it to invoice total. However sometimes the date will not match exactly so I have to find the previous date and return the exchange rate from that date. This is what the 'Select top 1 <= Inv.date..' is doing.
--The Update part--
If I find a date and process the Invoice, I want to update a Status field on the Invoice table to 'P' to indicate that this record has been processed and won't be picked up on a second run.

I now have the following
(as I had before)

Select
various fields
from tablea joined to two other tables
where
(a.date in (Select Top 1 a.date
from tableb
where a.date <= b.date2
order by a.date desc))
group by
various fields
order by b.date2

followed by the update below (with help from yourself, much obliged )which works fine.

update tablea set status = 'P'
from tablea joined to two other tables
where
(a.date in (Select Top 1 a.date
from tableb
where a.date <= b.date2
order by a.date desc))

However, I did wonder whether it was possible to have the update happen within the select statement as opposed to having it as a seperate statement after the original select. It is no big problem just wondered would it mean a massive restructuring of the code?

Again thanks for the help.

Cheers

D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 11:39:11

you can do it in single statement


UPDATE t
SET t.Invoice_Total=t.Invoice_Total * x.Excg_rate..., --your calculation here
t.Status='P'
FROM Invoice t
CROSS JOIN (SELECT TOP 1 Excg_rate
            FROM Exchnage_Rate
            WHERE Date <=t.Invoice_date
            ORDER BY Date DESC) x

i dont know what your actual columns are so just gave a sample query. make sure you replace your actual tables and columns in this.

Siopold
Starting Member

9 Posts

Posted - 2009-05-26 : 08:52:01

Thanks very much for the reply visakh16. I am not sure that is exactly what I want to do.
I want to run the SQL select to get some output but update within the SQL. Something like -

I would ideally want to display the selected data with the update performed.
Something like having the update within the overall select - see below

SELECT
various fields
from tablea joined to two other tables
where

--- Put the update here based upon the select criteria below in the ---IN clause
---all within the main (outer) ---SELECT

(a.date in (Select Top 1 a.date
from tableb
where a.date <= b.date2
order by a.date desc))
group by
various fields
order by b.date2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 11:49:27

you cant update within select. however if you're using sql 2005 or greater, you can perform update and then use new OUTPUT operator to play back the updated values

Siopold
Starting Member

9 Posts

Posted - 2009-05-26 : 13:12:43

Thanks visakh16. That is what I thought. Thanks for the reply and spending the time. I am used to programming (not in SQL) where you would simply read a record from a table, write to a report or file then update the status of the record you processed and read the next record in the loop.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 13:17:00

if your attempt was not to update the original table but do the calculation on the fly you can use expression directly in select to get the required field as derived field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources