Sql-server – Updating across row and column in SQL

sql serverupdate

Bit of a strange question. Entries in my table have three relevant columns:

drug
date_1
date_2
seq_id

I am trying to make it such that every drug with the lowest seq_id has a date_2 equal to the date_1 of the drug with the second lowest seq_id (the second lowest will always be lowest + 1) with the same drug name.

Does this make sense?

I don't have any real clue how to go about doing this. I've managed to make a query that returns the date_1 and seq_id of every second-lowest record, and now I want to make it such that every row with a seq_id exactly one lower than any seq_id in this set of records has date_2 = date_1 of the record with the 1-higher seq-id.

God this is really confusing, I hope it comes through.

Best Answer

The SQLServer version of this could look like this.

First we want the drug table and some values

create table #drugs (drug nvarchar(10),date_1 date, date_2 date, seq_id int)
 insert into #drugs values ('sildenafil',NULL,NULL,1)
                          ,('sildenafil',NULL,NULL,2)
                          ,('sildenafil',NULL,NULL,3)
                          ,('tadalafil',NULL,NULL,1)
                          ,('tadalafil',NULL,NULL,2)
                          ,('tadalafil',NULL,NULL,3)
                          ,('vardenafil',NULL,NULL,1)
                          ,('vardenafil',NULL,NULL,2)
                          ,('vardenafil',NULL,NULL,3)

Then we need to update the next lowest seq_id with a date

update #drugs 
  set date_1 = getdate() 
   where seq_id = 
   (select min(seq_id)+1 
    from #drugs d2 
    where #drugs.drug = d2.drug)

And the update the lowest date_2 value with the date_1 value from the second lowest seq_id - This is update with a join with the strange looking SQL-Server syntax and is

update D1
  set D1.date_2 = d2.date_1
  From #drugs D1
   inner join 
    (select drug,date_1,seq_id
     from #drugs d1 
     where seq_id = (select min(seq_id)+1 from #drugs)
    ) as d2
     on d1.drug = d2.drug and d1.seq_id = d2.seq_id-1

And Clean up

Drop Table #drugs

Related Solutions

Sql-server – Table not dropped correctly in SQL Server Express 2008

Whether using dbo or not, always specify the schema prefix explicitly. Otherwise, if you have users with different default schemas, you could end up with objects in a schema you didn't expect. One user creates a table in one schema, and the other can't see it. Here is a quick example:

CREATE SCHEMA floob AUTHORIZATION dbo;
GO

CREATE USER blat1 
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = floob;

CREATE USER blat2 
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;

GRANT CREATE TABLE TO blat1, blat2;
GRANT ALTER ON SCHEMA::floob TO blat1, blat2;
GRANT ALTER ON SCHEMA::dbo TO blat1, blat2;

EXECUTE AS USER = 'blat1';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

EXECUTE AS USER = 'blat2';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

Results:

-----  ---------
floob  Employees

-----  ---------
dbo    Employees
floob  Employees

SQL Server – Encrypt a Column Without Changing App Code

This is what Always Encrypted was created for, but you'll have to wait for SQL Server 2016 (and for the jTDS drivers to catch up).

In the meantime, your Java app could call stored procedures, such that the data is encrypted there. That's really not going to change the fact that the decrypted text is being sent over the wire, but it does mean you can isolate query changes from the app...

Best Answer

Related Solutions

Sql-server – Table not dropped correctly in SQL Server Express 2008

SQL Server – Encrypt a Column Without Changing App Code

Related Question