I have a very long query that is essentially an extension of the following:
update property.lease_period
set scca_uplift = '110',
scca_notes_code = '21006'
where (suite_id = 'CCBG08' and lease_id = '205059')
or (suite_id = 'CCBG14' and lease_id = '152424')
or (suite_id = 'CCCF048' and lease_id = '150659')
The where clause for this will have ~40 rows when complete. In order to make this task easier I was hoping to do something similar to the following:
update property.lease_period
set scca_uplift = '110',
scca_notes_code = '21006'
where suite_id in('CCBG08', 'CCBG14', 'CCCF048')
and lease_id in('205059', '152424', '150659')
Unfortunately lease_id isn't a unique field and there can be multiple lease_id's to the same suite_id (so subsequently the second query is unusable).
Is there a better way to do the first update statement given that this solution won't work?
You may create table type and pass the values thru it, like that:
CREATE TYPE Suite_Lease AS TABLE
(
suite_id varchar(15) NOT NULL,
lease_id varchar(15) NOT NULL
)
GO
CREATE PROC DoUpdate
@Params Suite_Lease READONLY,
@uplift varchar(15),
@code varchar(15)
AS
update property.lease_period set
scca_uplift = @uplift,
scca_notes_code = @code
from property.lease_period tab
JOIN @params filt
on tab.suite_id=filt.suite_id AND tab.lease_id=filt.lease_id
This will keep your Procedure cache dry and clean, instead if you using multiple "big" where clauses
How to pass table parameter into stored procedure (c#):
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("suite_id", typeof (string)) {AllowDBNull = false, MaxLength = 15});
dt.Columns.Add(new DataColumn("lease_id", typeof (string)) {AllowDBNull = false, MaxLength = 15});
dt.Rows.Add("CCBG08", "205059");
... add more rows for match
using (var c = new SqlConnection("ConnectionString"))
{
c.Open();
using(var sc = c.CreateCommand())
{
sc.CommandText = "DoUpdate";
sc.CommandType = CommandType.StoredProcedure;
sc.Parameters.AddWithValue("@uplift", "110");
sc.Parameters.AddWithValue("@code", "21006");
sc.Parameters.Add(new SqlParameter("@Params", SqlDbType.Structured) { TypeName = null, Value = dt });
sc.ExecuteNonQuery();
}
}
Using the trick from this article. This looks a bit ugly, but it does the trick:
update property.lease_period
set scca_uplift = @uplift, scca_notes_code = @code
from property.lease_period tab
JOIN (
select 'CCBG08' as suite_id, '205059' as lease_id union all
select 'CCBG14', '152424' union all
select 'CCCF048', '150659'
) xxx
on tab.suite_id=xxx.suite_id AND tab.lease_id=xxx.lease_id
Try this
update property.lease_period
set scca_uplift = '110',
scca_notes_code = '21006'
where (suite_id in,lease_id) in
(select suite_id in,lease_id from XXX_table where CONDITION)
The last SELECT should give you those 40 combinations.
Derived from a comment by @dasblinkenlight (for Oracle) another possible way to do this would be the following:
select *
from property.lease_period
where (suite_id + ' ' + lease_id)
in (
('CCBG08 205059'),
('CCBG14 152424'),
('CCCF048 150659')
)
This isn't very recommended as it would be bad for indexing (concatenation on MicrosoftSQL) however I thought it was interesting all the same.
dasblinkenlights original comment:
@Michael I wish you were asking about Oracle, it's a lot cleaner
there: you do where (lease_period,lease_id) in
(('CCBG08','205059'),('CCBG14','152424'),('CCCF048','150659')), and it
does the trick. Why SQL Server couldn't do it is beyond me. –