CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsql-update
Polina F.
Polina F.

Reputation: 649

How to select record from one of two tables by criteria , then update it

I have two tables - DataRecord and DataRecordArchive

New(unique dataRecordID - ex. 'id1') dataRecord inserted to DataRecord table with version 0. When (almost every) update is performed on some existing dataRecord (dataRecordID 'id1' already exists in DataRecord table) - the existing dataRecord (id :'id1' version :0 )is inserted to DataRecordArchive table and then the dataRecord is updated and the version changes to 1 (and so on to version 2 , 3 etc.). So in the end in table DataRecordArchive table the record is with id: 'id1' and version: 0 and in DataRecord table the record is with id: 'id1' and version: 1.

I need to update dataRecord with some value (bool value - no need to duplicate the row into DataRecordArchive table - for example 'correctFlag') , BUT I need to choose which table to update , because I have id and version , so the record to update might be in DataRecord table or in DataRecordArchive table. For example if I need to update dataRecord with id1 version 0 - the record is in DataRecordArchive table. If I need to update dataRecord with id1 version 1 the record is in DataRecord table.

Something like :

update 
 case when select count (*) from DataRecord where id = 'id1' and version=0 >0 then
DataRecord 
 when select count (*) from DataRecordArchive where id = 'id1' and version=0 >0 then
DataRecordArchive 
end
set correctFlag =1 where id= 'id1' and version = 0

What is the correct SQL statment for this update?

Upvotes: 0

Views: 48

Answers (3)

ManishKumar
ManishKumar

Reputation: 1554

You have to create an SP. In that SP, you can pass your data and then check the data, on the basis of version you can update tables.

For example:

create procedure update_data 

@version @int,
@id int,

As

if @version == 1
-- update in table1
else
-- update in table2

GO

Upvotes: 0

Sean Pearce
Sean Pearce

Reputation: 1169

Create a view that unions the two tables. Update the view.

Upvotes: 1

Jayesh Prakash
Jayesh Prakash

Reputation: 141

I cannot place a comment so will post as an answer. Ideally, we would need your table structure, sample data from your tables to suggest correctly. However, based on limited info I suggest to use dynamic sql. You will use dynamic sql to manipulate the from clause of your sql query based on conditions.

Please post table strucgture and sample data so that we can give you exact query to use.

Upvotes: 1

Related Questions