Sql Comparing two tables: update

Showing posts with label update. Show all posts

Thursday, March 29, 2012

concatenate problem

Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

ThanksOriginally posted by Devinder Gera
Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

Thanks
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint|||If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint [/SIZE][/QUOTE]

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks|||Originally posted by Devinder Gera
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks [/SIZE][/QUOTE]

No worries guys it started working. I was just updating at wrong time. I changed the location of update now its working fantastic

Thanks a million Saint

Thursday, March 22, 2012

computation for computed columns in sysservers

What is the computation for the sysservers table for the dataaccess and
isremote columns ? How can i find out the computation ?
Was trying to update the column and received a message that the column
cannot be modified since its a computed columnHassan,
Why are you trying to update system tables directly? This is not supported
and not advisable. Use sp_serveroption system SP instead.
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23D$4kKKzFHA.1264@.tk2msftngp13.phx.gbl...
> What is the computation for the sysservers table for the dataaccess and
> isremote columns ? How can i find out the computation ?
> Was trying to update the column and received a message that the column
> cannot be modified since its a computed column
>
>|||sp_addserver and sp_dropserver are the interfaces to that table.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:e8KWFVKzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hassan,
> Why are you trying to update system tables directly? This is not supported
> and not advisable. Use sp_serveroption system SP instead.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23D$4kKKzFHA.1264@.tk2msftngp13.phx.gbl...
>> What is the computation for the sysservers table for the dataaccess and
>> isremote columns ? How can i find out the computation ?
>> Was trying to update the column and received a message that the column
>> cannot be modified since its a computed column
>>
>|||just in general, if i have a computed column, how does one view the
computations tied to those columns ?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OQOlBeLzFHA.3660@.TK2MSFTNGP15.phx.gbl...
> sp_addserver and sp_dropserver are the interfaces to that table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:e8KWFVKzFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Hassan,
>> Why are you trying to update system tables directly? This is not
>> supported and not advisable. Use sp_serveroption system SP instead.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor
>> www.SolidQualityLearning.com
>>
>> "Hassan" <hassanboy@.hotmail.com> wrote in message
>> news:%23D$4kKKzFHA.1264@.tk2msftngp13.phx.gbl...
>> What is the computation for the sysservers table for the dataaccess and
>> isremote columns ? How can i find out the computation ?
>> Was trying to update the column and received a message that the column
>> cannot be modified since its a computed column
>>
>>
>|||Seems to be in syscomments.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <hassanboy@.hotmail.com> wrote in message news:%23nsmjDVzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> just in general, if i have a computed column, how does one view the
> computations tied to those columns ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OQOlBeLzFHA.3660@.TK2MSFTNGP15.phx.gbl...
>> sp_addserver and sp_dropserver are the interfaces to that table.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
>> message news:e8KWFVKzFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Hassan,
>> Why are you trying to update system tables directly? This is not
>> supported and not advisable. Use sp_serveroption system SP instead.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor
>> www.SolidQualityLearning.com
>>
>> "Hassan" <hassanboy@.hotmail.com> wrote in message
>> news:%23D$4kKKzFHA.1264@.tk2msftngp13.phx.gbl...
>> What is the computation for the sysservers table for the dataaccess and
>> isremote columns ? How can i find out the computation ?
>> Was trying to update the column and received a message that the column
>> cannot be modified since its a computed column
>>
>>
>>
>|||In Query Analyzer, press F8 to open the Object Browser, right-click the
table, click Script to new window as > Create.
Don't ever alter or update system tables directly.
--
David Portas
SQL Server MVP
--|||Sure they are, but not the only ones; actually, they are very basic ones.
You add linked servers with sp_addlinkedserver; you change the server
options, like data access, with sp_serveroption, as I correctly mentioned.
All these procedures are described in BOL.
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OQOlBeLzFHA.3660@.TK2MSFTNGP15.phx.gbl...
> sp_addserver and sp_dropserver are the interfaces to that table.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:e8KWFVKzFHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Hassan,
>> Why are you trying to update system tables directly? This is not
>> supported and not advisable. Use sp_serveroption system SP instead.
>> --
>> Dejan Sarka, SQL Server MVP
>> Mentor
>> www.SolidQualityLearning.com
>>
>> "Hassan" <hassanboy@.hotmail.com> wrote in message
>> news:%23D$4kKKzFHA.1264@.tk2msftngp13.phx.gbl...
>> What is the computation for the sysservers table for the dataaccess and
>> isremote columns ? How can i find out the computation ?
>> Was trying to update the column and received a message that the column
>> cannot be modified since its a computed column
>>
>>
>

Sunday, March 11, 2012

Complicated Update Statement

Hi All,

I need to write one complicated update statement and I'm looking at
maybe finding a simpler way to do it.

I have 2 tables:

1.Photo Table
PhotoID FileName
1 111.jpg
1 111_01.jpg
2 222.jpg
2 222_01.jpg
2 222_02.jpg
3 333.jpg

2.PhotoReport Table
PhotoID FileName1 FileName2 FileName3....FileName12
1 111.jpg 111_01.jpg NULL NULL
2 222.jpg 222_01.jpg 222_02.jpg
..
..
..
I need to update PhotoReport Table to look like an example above. I've
started writing my code and it looks very hedeous with multiple nested
cursors.
So if someone has a sample of a code to accomplish this, please pretty
please send it my way. I appreciate it in advance.

Thank you,
Narine"Narine" <narine.kostandyan@.prurealty.com> wrote in message
news:5628948a.0406111054.2e24cb1b@.posting.google.c om...
> Hi All,
> I need to write one complicated update statement and I'm looking at
> maybe finding a simpler way to do it.
> I have 2 tables:
> 1.Photo Table
> PhotoID FileName
> 1 111.jpg
> 1 111_01.jpg
> 2 222.jpg
> 2 222_01.jpg
> 2 222_02.jpg
> 3 333.jpg
> 2.PhotoReport Table
> PhotoID FileName1 FileName2 FileName3....FileName12
> 1 111.jpg 111_01.jpg NULL NULL
> 2 222.jpg 222_01.jpg 222_02.jpg
> .
> .
> .
> I need to update PhotoReport Table to look like an example above. I've
> started writing my code and it looks very hedeous with multiple nested
> cursors.
> So if someone has a sample of a code to accomplish this, please pretty
> please send it my way. I appreciate it in advance.
> Thank you,
> Narine

You're looking for a crosstab query:

http://www.aspfaq.com/show.asp?id=2462

Simon|||[posted and mailed, please reply in news]

Narine (narine.kostandyan@.prurealty.com) writes:
> I need to write one complicated update statement and I'm looking at
> maybe finding a simpler way to do it.
> I have 2 tables:
> 1.Photo Table
> PhotoID FileName
> 1 111.jpg
> 1 111_01.jpg
> 2 222.jpg
> 2 222_01.jpg
> 2 222_02.jpg
> 3 333.jpg
> 2.PhotoReport Table
> PhotoID FileName1 FileName2 FileName3....FileName12
> 1 111.jpg 111_01.jpg NULL NULL
> 2 222.jpg 222_01.jpg 222_02.jpg
> .
> .
> .
> I need to update PhotoReport Table to look like an example above. I've
> started writing my code and it looks very hedeous with multiple nested
> cursors.
> So if someone has a sample of a code to accomplish this, please pretty
> please send it my way. I appreciate it in advance.

Cursors? You don't need cursors for this! But you here you get a
12-way self-join. Here I use a temp table, to make this a little
less verbose and more efficient:

CREATE TABLE photos (id int NOT NULL,
filename varchar(23) NOT NULL,
CONSTRAINT pk_photos PRIMARY KEY (id, filename))
go
CREATE TABLE photoreport (id int NOT NULL,
filename1 varchar(23) NOT NULL,
filename2 varchar(23) NULL,
filename3 varchar(23) NULL,
filename4 varchar(23) NULL,
filename5 varchar(23) NULL,
filename6 varchar(23) NULL,
filename7 varchar(23) NULL,
filename8 varchar(23) NULL,
filename9 varchar(23) NULL,
filename10 varchar(23) NULL,
filename11 varchar(23) NULL,
filename12 varchar(23) NULL DEFAULT 'test',
CONSTRAINT pk_report PRIMARY KEY(id))
go
INSERT photos (id, filename) values (1, '111.jpg')
INSERT photos (id, filename) values (1, '111_01.jpg')
INSERT photos (id, filename) values (2, '222.jpg')
INSERT photos (id, filename) values (2, '222_01.jpg')
INSERT photos (id, filename) values (2, '222_02.jpg')
INSERT photos (id, filename) values (3, '333.jpg')
INSERT photos (id, filename) values (3, '333_01.jpg')
INSERT photos (id, filename) values (3, '333_03.jpg')
go
INSERT photoreport (id, filename1)
SELECT id, MIN(filename)
FROM photos
GROUP BY id
go
SELECT id, filename,
rowno = (SELECT COUNT(*)
FROM photos p2
WHERE p1.id = p2.id
AND p1.filename >= p2.filename)
INTO #temp
FROM photos p1
ORDER BY id, filename

UPDATE photoreport
SET filename1 = t1.filename,
filename2 = t2.filename,
filename3 = t3.filename,
filename4 = t4.filename,
filename5 = t5.filename,
filename6 = t6.filename,
filename7 = t7.filename,
filename8 = t8.filename,
filename9 = t9.filename,
filename10 = t10.filename,
filename11 = t11.filename,
filename12 = t12.filename
FROM photoreport r
JOIN #temp t1 ON t1.id = r.id
AND t1.rowno = 1
LEFT JOIN #temp t2 ON t2.id = r.id
AND t2.rowno = 2
LEFT JOIN #temp t3 ON t3.id = r.id
AND t3.rowno = 3
LEFT JOIN #temp t4 ON t4.id = r.id
AND t4.rowno = 4
LEFT JOIN #temp t5 ON t5.id = r.id
AND t5.rowno = 5
LEFT JOIN #temp t6 ON t6.id = r.id
AND t6.rowno = 6
LEFT JOIN #temp t7 ON t7.id = r.id
AND t7.rowno = 7
LEFT JOIN #temp t8 ON t8.id = r.id
AND t8.rowno = 8
LEFT JOIN #temp t9 ON t9.id = r.id
AND t9.rowno = 9
LEFT JOIN #temp t10 ON t10.id = r.id
AND t10.rowno = 10
LEFT JOIN #temp t11 ON t11.id = r.id
AND t11.rowno = 11
LEFT JOIN #temp t12 ON t12.id = r.id
AND t12.rowno = 122

SELECT * FROM photoreport
go
DROP TABLE #temp, photoreport, photos
go

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Simon and Erland,

Thank you very much for helping me out here.
Erland's example worked for me because in my case I had to generate a
rownum column to be able to use the Case statement.

Thanks a million,
Narine

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Complicated Update query based on existing data

OK folks, may have a tough one or perhaps just not thinking it through
well. I need to create a SQL query or queries that updates two columns
based on some business rules. Here's an example of the data:
GroupID complete_num first_num second_num InUse biggest
965423 1.0 1 0
965423 2.0 2 0
965423 3.0 3 0 X
965423 3.1 3 1
965423 3.2 3 2 X
324554 1.0 1 0
324554 2.0 2 0 X X
123456 0.1 0 1
123456 0.2 0 2 X X
Hopefully the above even vaguely lines up for you. The last two
columns are currently blank. The representation above is how I would
like them to look after the queries run. So for the above data, you
have a group id that links all records for one set together. I need to
have the "InUse" box updated with a value of "X" for the highest number
that has zero in the second_num column for a group. I also need the
biggest column set to "X" for the largest number in a particular group,
so 3.1 is larger than 3.0. Keep in mind, I have separated the
complete_num into two columns as there could be a "decimal" value of
"10" which is higher than "1". They are not the same as complete_num
is not really a decimal numeric representation. It's used
programatically for other things. Seperating into two separate columns
allows better sorting of data as complete_num is varchar and first and
second num are integer. You will also see the case, 123456,where there
is no zero in the second_num column. In this case, the highest
second_num value will have both set to "X". Any help would be
appreciated and SQL queries are prefered over any procedures/functions
as this is a one time query I need to run against the database. If you
need further clarification or more examples, please let me know.
Thanks.
JRAlso note that there is of course a unique incremental column in the
table. For arguements sake we can say U_ID. Also these records could
be in any order by default in the database. Not necessarily grouped
together as shown above.|||JR (jriker1@.yahoo.com) writes:
> OK folks, may have a tough one or perhaps just not thinking it through
> well. I need to create a SQL query or queries that updates two columns
> based on some business rules. Here's an example of the data:
> GroupID complete_num first_num second_num InUse biggest
> 965423 1.0 1 0
> 965423 2.0 2 0
> 965423 3.0 3 0 X
> 965423 3.1 3 1
> 965423 3.2 3 2 X
> 324554 1.0 1 0
> 324554 2.0 2 0 X X
> 123456 0.1 0 1
> 123456 0.2 0 2 X X
> Hopefully the above even vaguely lines up for you. The last two
> columns are currently blank. The representation above is how I would
> like them to look after the queries run. So for the above data, you
> have a group id that links all records for one set together. I need to
> have the "InUse" box updated with a value of "X" for the highest number
> that has zero in the second_num column for a group. I also need the
> biggest column set to "X" for the largest number in a particular group,
> so 3.1 is larger than 3.0.
It is always a good idea for this sort of question to include CREATE
TABLE statements for the table, and the sample data as INSERT statements.
That makes it easy to copy-and-paste into a query tool, to develop a
tested solution.
Thus, this is an untested solution:
BEGIN TRANSACTION
UPDATE tbl
SET biggest = 0,
inuse = 0
UPDATE tbl
SET biggest = 1
FROM tbl a
WHERE EXISTS (SELECT *
FROM (SELECT GroupID,
biggest = MAX(100000 * first_num + second_num)
FROM tbl
GROUP BY GroupID) AS big
WHERE a.big = big.GroupID
AND a.first_num * 1000000 + a.second_num = big.biggest)
UPDATE tbl
SET inuse = 1
FROM tbl a
JOIN (SELECT GroupID, first_num = MAX(first_num)
FROM tbl
WHERE second_num = 0
GROUP BY GroupID) AS inuse ON a.GroupID = inuse.GroupID
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE tbl
SET inuse = 1
FROM tbl a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.GroupID = b.GroupID
AND a.insue = 1)
COMMIT TRANSACTION
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. SQL Query Analyser is complaining about a syntax
problem with the '=' on "biggest = MAX(100000 * first_num +
second_num)" in the second update and "JOIN (SELECT GroupID,
first_num = MAX(first_num)" in the ghird.|||JR,
I think you can say this more simply: Update InUse to X
for the largest second_num value in the group *counting 0
as the largest*, and the largest first_num value in the case of
a tie. Update biggest to X for the largest first_num value
in the group, and the largest second_num value in the case
of a tie.
There is a more compact solution than Erland's in SQL Server 2005:
with T2 as (
select
*,
rank() over (partition by GroupID order by first_num desc,
case when second_num = 0 then 2147364827 else second_num end desc)
as rk1,
rank() over (partition by GroupID order by second_num desc,
first_num desc) as rk2
from #T
)
update T2 set
InUse = case when rk1 = 1 then 'X' else InUse end,
biggest = case when rk2 = 1 then 'X' else biggest end
where rk1 = 1 or rk2 = 1
Steve Kass
Drew University
JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
>GroupID complete_num first_num second_num InUse biggest
>965423 1.0 1 0
>965423 2.0 2 0
>965423 3.0 3 0 X
>965423 3.1 3 1
>965423 3.2 3 2 X
>324554 1.0 1 0
>324554 2.0 2 0 X X
>123456 0.1 0 1
>123456 0.2 0 2 X X
>
>Hopefully the above even vaguely lines up for you. The last two
>columns are currently blank. The representation above is how I would
>like them to look after the queries run. So for the above data, you
>have a group id that links all records for one set together. I need to
>have the "InUse" box updated with a value of "X" for the highest number
>that has zero in the second_num column for a group. I also need the
>biggest column set to "X" for the largest number in a particular group,
>so 3.1 is larger than 3.0. Keep in mind, I have separated the
>complete_num into two columns as there could be a "decimal" value of
>"10" which is higher than "1". They are not the same as complete_num
>is not really a decimal numeric representation. It's used
>programatically for other things. Seperating into two separate columns
>allows better sorting of data as complete_num is varchar and first and
>second num are integer. You will also see the case, 123456,where there
>is no zero in the second_num column. In this case, the highest
>second_num value will have both set to "X". Any help would be
>appreciated and SQL queries are prefered over any procedures/functions
>as this is a one time query I need to run against the database. If you
>need further clarification or more examples, please let me know.
>Thanks.
>JR
>
>|||On 9 Apr 2006 07:36:13 -0700, JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
(snip)
Hi JR,
If these columns should be calculated based on the data, why store them
at all? I would only recommend that if updates are very infrequent,
queries of the data are very frequent and the table is large, or if
these columns need to capture a moment in time and stay unchanged after
that, even if the underlying data does change.
In one of the latter cases, use Erland's suggestion. Otherwise, drop the
columns from the table and set up a view instead:
CREATE VIEW ChooseGoodName
AS
SELECT a.GroupID, a.complete_num, a.first_num, a.second_num,
CASE WHEN a.first_num = b.first_num
AND a.second_num = 0
THEN 'X' -- Latest X.0
WHEN a.first_num = 0
AND a.second_num = b.second_num
THEN 'X' -- Latest 0.Y
ELSE ''
END AS InUse,
CASE WHEN b.first_num = a.first_num
AND b.second_num = a.second_num
THEN 'X'
ELSE ''
END AS biggest
FROM YourTable AS a
INNER JOIN (SELECT GroupID, complete_num, first_num, second_num
FROM YourTable AS c
WHERE NOT EXISTS (SELECT *
FROM YourTable AS d
WHERE d.GroupID = c.GroupID
AND ( d.first_num > c.first_num
OR ( d.first_num = c.first_num
AND d.second_num > c.second_num))
) ) AS b
ON b.GroupID = a.GroupID
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||JR (jriker1@.yahoo.com) writes:
> Thanks Erland. SQL Query Analyser is complaining about a syntax
> problem with the '=' on "biggest = MAX(100000 * first_num +
> second_num)" in the second update and "JOIN (SELECT GroupID,
> first_num = MAX(first_num)" in the ghird.
Yes, as I said the code was untested for reasons I explained. I assume
that you are able to weed out trivial syntax errors on your own. If not,
please include CREATE TABLE statements and the sample data in INSERT
statements, to make testing easy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As requested, here is a table creation script and some minimal data to
limit the length of the message. Also Hugo's view is great however
since this data is for one time use, would imagine updates to the
existing data would be preferable over introducing a new view into the
mix. If not updating the data directly based on the view would now be
a simple matter when you introduce the Id from the original table into
the view.
CREATE TABLE [abcd].[dbo].[TBL1
(Id,GroupId,complete_num,first_num,secon
d_num)] (
[Id] int NOT NULL,
[GroupID] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[InUse] varchar (2) NULL,
[biggest] varchar (2) NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(91510,ABC1235,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89377,ABC1235,2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89371,ABC1235,2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1310,M123456,1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1309,M123456,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1311,M123456,3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1312,M123456,4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1315,M123456,5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1318,M123456,6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1319,M123456,7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1317,M123456,8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5342,M123456,9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5346,M123456,10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5756,M123456,11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6315,M123456,12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6604,M123456,13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6920,M123456,14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1002,M123456,15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(4023,ARDFO32,0.1,0,1);|||Your programs will be total nightmares and crap until you learn how to
design a schema.
Look at the DDL; do you really have a NCHAR(60) grpoup identifier? In
Chinese' Wel;l;, since you allowed it, you will get one! Why do you
have redundant split attributes (i.e. first_num || second_num =
complete_num)? Let's spit on normalization!! I also love the clear,
meaningful names of the data elements. Tell us what a thing is, not
its sequential order inside another column. Logical not physical
descriptions.
There are no keys, no constraints. This is not a table at all! And
you invented your own syntax for CREATE TABLE.
Your idea of updatind computed columns is a way to mimic punch cards.
Back in the 1950-60's we had to store those things in the physical
card, like you are doing now.
Making a guess, if you normalized your schema, had a key and followed
the baisc data modeling rules, would this nightmare look more like
this? Better names that show subordination (well, Foobar is a dummy
name, but that is all you gave us)
CREATE TABLE Foobar
(group_id CHAR(6) NOT NULL
CHECK (group_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
section_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (section_num >= 0),
subsection_nbr INTEGER DEFAULT 0 NOT NULL
CHECK (subsection_num >= 0),
PRIMARY KEY (group_id, section_nbr, subsection_nbr));
Do you need a constaint to assure that the subsections are in sequence?
Is there a check digit rule in the group_id? 90% of the work in RDBMS
is done in the DDL!!
SQL does not have links; it has REFERENCES and grouping. Totally
different concepts, based on sets and not pre-RDBMS file and pointer
systems. Rows are nothing whatsoever like records.
Now, the answer to your question is a VIEW, not a "punch cards and bit
flags" solution via updates.
CREATE VIEW InUseFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, MAX(section_nbr), 0
FROM Foobar
WHERE subsection = 0
GROUP BY group_id) ;
CREATE VIEW MaxFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, section_nbr, MAX(subsection_nbr)
FROM Foobar AS F1, InUseFoobar AS U1
WHERE F1.group_id = .U1.group_id
AND F1.section_nbr = .U1.section_nbr
GROUP BY group_id, section_nbr ;
<<untested>>|||JR (jriker1@.yahoo.com) writes:
> As requested, here is a table creation script and some minimal data to
> limit the length of the message. Also Hugo's view is great however
> since this data is for one time use, would imagine updates to the
> existing data would be preferable over introducing a new view into the
> mix. If not updating the data directly based on the view would now be
> a simple matter when you introduce the Id from the original table into
> the view.
Below is a tested version of my script. I'm not sure that I understand
the syntax errors you mentioned; I did not get these. I include
your original script, with small changes. Note that I've made inuse
and biggest into bit columns; I did this as I used 0 and 1 in my script.
Beware that things get wrapped in news transport!
CREATE TABLE [dbo].[TBL1]
(
[Id] int NOT NULL,
[GroupId] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[inuse] bit NULL,
[biggest] bit NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (9151
0,'ABC1235',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
7,'ABC1235',2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
1,'ABC1235',2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1310
,'M123456',1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1309
,'M123456',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1311
,'M123456',3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1312
,'M123456',4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1315
,'M123456',5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1318
,'M123456',6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1319
,'M123456',7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1317
,'M123456',8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5342
,'M123456',9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5346
,'M123456',10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5756
,'M123456',11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6315
,'M123456',12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6604
,'M123456',13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6920
,'M123456',14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1002
,'M123456',15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (4023
,'ARDFO32',0.1,0,1);
go
BEGIN TRANSACTION
UPDATE TBL1
SET biggest = 0,
inuse = 0
UPDATE TBL1
SET biggest = 1
FROM TBL1 a
WHERE EXISTS (SELECT *
FROM (SELECT GroupId,
biggest = MAX(100000 * first_num + second_num)
FROM TBL1
GROUP BY GroupId) AS big
WHERE a.GroupId = big.GroupId
AND a.first_num * 100000 + a.second_num = big.biggest)
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
JOIN (SELECT GroupId, first_num = MAX(first_num)
FROM TBL1
WHERE second_num = 0
GROUP BY GroupId) AS inuse ON a.GroupId = inuse.GroupId
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM TBL1 b
WHERE a.GroupId = b.GroupId
AND b.inuse = 1)
COMMIT TRANSACTION
SELECT * FROM TBL1
go
drop table TBL1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

complicated update (for me)

hello everyone,
just having trouble with an update statement. so i started off with a
table like so:
create table foo (
id uniqueidentifier not null primary key nonclustered,
name varchar(50) not null,
type int not null,
datecreated datetime not null default getdate(),
constraint foo1 unique (name)
)
go
then i added a new column, like so:
alter table foo add column rank int null
go
and here's where the tricky update comes in. my goal is to populate
this new "rank" column with incremental values for each distinct "type"
value.
for example, i the original table looks like so:
select name, type from foo
go
name type
-- --
john 1
jane 1
jim 1
jake 2
jeff 2
kyle 2
keli 2
kim 2
i would want the update statement to populate the table so that AFTER
the update, it looks like so:
select name, type, rank from foo
name type rank
-- -- --
john 1 1
jane 1 2
jim 1 3
jake 2 1
jeff 2 2
kyle 2 3
keli 2 4
kim 2 5
it's important to note that the original "rank" really doesn't matter,
it is just something that needs to be kept track of moving forward, so
i don't even particularly have to rank them alphabetically, or any
other way. just need an update statement that can put in incremental
values, but that sort of "resets" the incrementation for each changing
value of another field.
if that's possible.
thanks in advance for any help!
jasonYou have a few problems with this. First, a uniqueidentifier cannot be
a relational key, so this is not a table by definition. Where does it
occur in the reality of the data model? Next, Rows are not records;
fields are not columns; tables are not files. Totally differerent
concepts. You are building a sequential file in SQL
DEFAULT comes after the data type in STANDARD SQL, and you can use
CURRENT_TIMESTAMP instead of the proprietary getdate(). But you should
not put audit trail information in the table (ask your accountant about
proper procedures).
There is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless. So what are you using to
assign the rank values? The data model should have some rule so you
can validate the data.
Granted this is a sample table, but all of the column names are
incomplete (type of what? Name of what? Etc.) Your sample design
should look more like this:
CREATE TABLE NewFoo
(foo_name VARCHAR(50) NOT NULL PRIMARY KEY,
foo_type INTEGER NOT NULL,
bar_rank INTEGER NOT NULL,
UNIQUE (foo_type, bar_rank)); -- is this the PK?
Now, let's kill the old one and get things into an RDBMS:
INSERT INTO NewFoo (foo_name, foo_type, bar_rank)
SELECT name, type,
(SELECT COUNT(F1.*)
FROM Foo AS F1
WHERE F1.type = Foo.type
AND F1.name = Foo.name)
FROM Foo;
Then drop Foo and rename NewFoo.|||> First, a uniqueidentifier cannot be a relational key, so this
> is not a table by definition. Where does it occur in the
> reality of the data model?
i call bs on this one, CELKO. a uniqueidentifier is a perfectly valid
artificial key for any record. what makes you think it can't be a
relational key?

> But you should not put audit trail information in the table
i call bs on this one too, i'm afraid. row-level auditing is a
perfectly valid practice for a number of reasons.
as for the rest, yeah, i was lazy, i didn't actually try the create
table statement before posting :) and i'll try to be more descriptive
in future examples.
as for the rank, it's definitely not an attempt to make the table a
sequential file, so you guessed wrong there i'm afraid. i use clustered
indexes if order is important for something, which has specific,
uncommon purposes in my opinion.
the rank is a meaningful value that a subscriber application needs to
know. you can think of it like your standing in a contest, you're
either in first, second, third, etc. place. the rank can't actually be
determined by any values in the database, it is determined by the
providers of the data, and we just store the rank. it's meaningful, not
physically sequential, etc.
and lastly, regarding the insert statement: THANK YOU. exactly what i
was looking for. i look forward to trying it out when i get back to the
office tomorrow.
thankful as always,
jason|||>> call bs on this one, CELKO. a uniqueidentifier is a perfectly valid artificial
key for any record [sic]. what makes you think it can't be a relational key? <<
The very definition of a relationall key, the stuff Dr. Codd wrote and
basic data model concepts. A relational key is subset of the
attributes of an entity that is unique. A uniqueidentifier is derived
from physical storage and has nothing whatsoever to do with the entity.
Newbies who do not know that a row (logical construct) is nothing like
a record (physical storage) constantly make this mistake and build file
systems in SQL.
An artificial key has to have validation and verification rules, and a
uniqueidentifier does not.
That is fine, but putting the audit trail into the table that is being
audited is not a proper accounting practice. The changes need to be
caught outside of the table. Talk to the accounting department or the
SOX guy for your company. This is like letting developers do their own
QA.|||assuming (name, type) is unique
update foo set rank = (select count(*) from foo f1 where f1.type =
foo.type and f1.name<foo.name)+1
if (name, type) is NOT unique, it it still doable but more complex|||> A uniqueidentifier is derived from physical storage
well, I guess whoever says this at a job interview is less likely to
get hired ;)|||> A relational key is subset of the
> attributes of an entity that is unique.
actually, if i'm reading you correctly, that's a called NATURAL key.
relational keys do not have the necessary condition of being natural
elements of an entity. the only necessary condition of a relational key
is that it be a column or columns whose values are gauranteed to be
unique across all occurrences in a given table. that's it.
by this definition, a relational key can be natural OR artificial. what
you're describing is a natural relational key, and good for you, that's
totally fine. and so are artificial relational keys.

> An artificial key has to have validation and verification rules, and a
> uniqueidentifier does not.
the only "validation and verification" rule required to act as a
relational key is that it be UNIQUE. and uniqueidentifiers, when
properly used, are certainly that.
i presume that you would have just as many objections about using an
identity integer as a relational key? if that's true, then you're
grossly misrepresenting your argument. you're not arguing the
definition of a relational key, you're arguing the validity of
artificial versus natural keys AS relational keys. totally different
argument.

> That is fine, but putting the audit trail into the table that is being
> audited is not a proper accounting practice.
i don't see your logic here. what is the difference between attaching
such a column to the entity it is auditing and putting it in another
table, and relating it to the entity it is auditing? the only
difference i can think of is that you could apply different user
permissions to each table. that's fine and well, but there are plenty
of other places to handle security, and other considerations, such as
performance.|||this worked like a charm, thank you very much!
jason|||yeah, i wasn't sure where that was coming from either. aren't they
derived from like a bunch of crazy variables? datetime, cpu serial
number, mac address, your mother's maiden name, the position of the
every valence electron in your body ...|||>> what is the difference between attaching such a column to the entity it i
s auditing and putting it in another table, and relating it to the entity it
is auditing? <<
You do not have to put the audit information in the schema at all. It
can be in an external file system or other RDBMS.
Separation is a basic accounting principle, like double entry
bookkeeping. For example, when I submit an article to a publisher, I
send the editor one copy of the invoice and another copy to Accounts
Payable. The A/P clerk has to match both copies of the invoice before
they issue a check to me.
My editor deletes their copies of my invoice. The A/P clerk now has an
invoice without a mate at the end of the payment cycle, so they know to
start calling people.
The A/P department deletes their copies of my invoice. The editor now
has an invoice which was no paid at the end of the payment cycle, so
they know to call the A/P department.
Both editor and A/P delete their copies of my invoice. Accounting sees
a missing invoice number at the end of the payment cycle, because they
designed an invoice number that can be validated and verified rather
than a meaningless, hardware generated number. Accounting makes life
hell for everyone until they can trace that missing invoice number.
.

Complicated question about update procedures

My company works with a fairly large database that needs to be kept live.
My problem is that we need a method of updating the database without messing the data, or crashing the website.
I am sure this is a common problem with a number of solutions. Couldanyone please direct me to a good article on the best practices forupdating databases like this?
What do you mean by updating the database? Just inserting records o chaging its structure?
|||Anything. Inserting records is not a problem. It is changing the structure which is.
When I am developing, I dont want to be working on the live database.
But if I copy the database and edit the copy, what happens to any new data on the live database?
Hope that is not confusing.
|||When you make updates to the structure of the database you should consider whether it affects the behaviour of the application or not. If it doesn′t you can make the update at anytime (But it will be better to schedule it when there is no heavy traffic). If it affects the application, the update should be accompanied with an update in the application. In this case you should schedule a maintainance stop (obviously the users should be adviced with anticipation), and use that time to update everything, the application, the database, configurations, etc. I also work for a huge company with a lot of servers, databases and web applications running, and this is the way we behave.
|||

I was just wondering what the best approach to this kind of thing is.

For example, lets say I have version 1.0 of an application with a db backend. Now, when I start creating version 2, the application and db will change. Do I :

1. Edit the live db making extra care that version 1.0 is not affected by the changes. This has its obvious problems - one mistake and the whole application goes down.

2. Create a copy of the db, edit that, and then link that in with version 2 when it is ready. The problem with this is that, between the time ot copying the db and releasing version 2.0, the users are still using version 1.0 of the db. I would have to then copy that over. This isnt a problem with minor changes, but it is when I start changing/creating keys and constraints (especially in sql server).
So I was wondering what the industry works around creating the next version of software without corrupting the current version.
I know there is no right answer - it depends on the situation. But any guidence would be appriciated.

|||

jagdipa wrote:
I know there is no right answer - it depends onthe situation. But any guidence would be appriciated.

I don't know that I agree with that statement. I think there is aright answer. It is called the Development, Staging, Productionmodel (DSP). The Staging database starts out as a mirror image ofthe Production database. Update scripts are run and tested on theStaging database until the consistent desiredresult is reached, with the Staging database being restored with abackup of theProduction database before each cycle of tests. This way, whenyou are ready to go live with your changes, you simple run the updatescripts that have been tested and perfected on the Staging databaseagainst your Production database.
Check out these links:
The Development, Staging, and Production Model
Setting up a DSP Environment -- look especially at the Managing Database Development section, and the Staging Environment section

|||Brilliant. Exactly what I was looking for.
I am just wondering if DSP is a standard method the industry uses, and are there other methods applied to this problem?
Again, thank you for the guidance
Jagdip
|||Just wondering if you have any more links about DSP?
|||Hi Jagdip, I was hoping someone else might chime in. I have noidea if the DSP approach is the industry standard, but it's the methodI've come to employ as a best practice for myself (in ideal situationsat least, sometimes some of the steps have been shortcut) and it is theway I've noticed some of my peers have worked. I've only recentlypicked up on the term "DSP" to be honest. When I read thedescription of the acronym I said to myself, "oh, I didn't know therewas a term appplied to this approach".
I just Googled a bit more and came up with this, which uses the DSP approach without using the term:
Migration to Production
I don't have many further resources for you, I'm afraid. The keyis to make your rollout to production consistently repeatable in yourstaging environment. This way when you go live you have minimizedyour risks as much as possible. I can't imagine an approach thatwould be better.

|||The only real problem we have is with updating the database. Writtingthe SQL scripts and saving then is a great way of keeping documentationon DB updates as well as keeping the existing data. Up until now, mycolleage has been working on the live database!!!!
I have had a word with him and he likes it. So thank you for theadvice. I'm in the process of writting and testing a procedure usingDSP, and I will have a look at the other article you gave me.
I guess the only real problem I have with DSP is that it will reallyslow down our RAD ideal. But the boss asked for something like this, sohe's going to have to live with it :-)

Thursday, March 8, 2012

Complex UPDATE

Given the 2 tables Sponsor and SponsorEvent with SponsorID on SponsorEvent
as a FK
CREATE TABLE [dbo].[Sponsor] (
[SponsorID] [int] NOT NULL ,
[SponsorCode] [char] (15) NOT NULL ,
[BrandID] [int] NOT NULL ,
[FirstDate] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sponsor] WITH NOCHECK ADD
CONSTRAINT [PK_Sponsor] PRIMARY KEY CLUSTERED
(
[SponsorID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[SponsorEvent] (
[Date] [int] NOT NULL ,
[BarbChanID] [int] NOT NULL ,
[StartTime] [int] NOT NULL ,
[AreaFlags] [smallint] NOT NULL ,
[PlatformFlags] [tinyint] NOT NULL ,
[EndTime] [int] NOT NULL ,
[SponsorID] [int] NOT NULL ,
[SponsorICodeID] [int] NOT NULL ,
[SponsorINameID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[SponsorEvent] WITH NOCHECK ADD
CONSTRAINT [PK_SponsorEvent] PRIMARY KEY CLUSTERED
(
[Date],
[BarbChanID],
[StartTime],
[AreaFlags],
[PlatformFlags]
) WITH FILLFACTOR = 90 ON [PRIMARY]
what is wrong with this syntax
update Sponsor set FirstDate = att.[Date]
from (
SELECT SponsorID,MIN([DATE]) From SponsorEvent GROUP by SponsorID)
att,Sponsor s
where s.SponsorID = att.SponsorID
Query Analyser complains of
"No column was specified for column 2 of 'att'."
I am trying to group by SponsorID on SponsorEvent, work out what the minimum
date is on SponsorEvent and update [Date] on Sponsor with this where
SponsorID is in common.
Thanks
Stephen Howe> Query Analyser complains of
> "No column was specified for column 2 of 'att'."
Isn't it "No column NAME was specified...?"
This happens when you apply a calculation on a column and don't include an
alias. The outer query doesn't see a column named "Date", it only sees the
expression MIN([Date]), which is not the same thing.
How about :
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor
INNER JOIN
(
SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att
ON s.SponsorID = att.SponsorID
Or, better yet, instead of having to run this update every single time the
SponsorEvent changes, drop the column firstdate from the sponsor table.
There is no reason to store this redundant information when you can always
get it directly from SponsorEvent.
As an aside, I recommend renaming the column [Date]. It is never a good
idea to use reserved words as column names.
A|||Sorry, forgot the s:

> FROM Sponsor
Should be
FROM Sponsor s|||Thanks for response

> Isn't it "No column NAME was specified...?"
No. Definitely not. What I wrote was copy and pasted out of QA.
There is a message in colour red just before. The full message is
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'att'.

> How about :
> UPDATE Sponsor
> SET FirstDate = att.FirstDate
> FROM Sponsor
> INNER JOIN
> (
> SELECT SponsorID, [Date] = MIN([DATE])
> FROM SponsorEvent
> GROUP by SponsorID
> ) Att
> ON s.SponsorID = att.SponsorID
Does not work. I changed text to
SET FirstDate = att.[Date]
FROM Sponsor s
and now I get
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'att' does not match with a table name or alias name used
in the query."

> Or, better yet, instead of having to run this update every single time the
> SponsorEvent changes, drop the column firstdate from the sponsor table.
> There is no reason to store this redundant information when you can always
> get it directly from SponsorEvent.
Yes you are right and I would like to.
But the reason why is that we have a analogous situation for tables where
Sponsor and SponsorEvent which have SponsorID in common
is reproduced with
SpotFilm and Spot which have SpotFilmID in common
SpotFilm and Sponsor have identical columns apart from the ID fields. It is
also such that if you concatenated them, the ID's are unique, no row in
common.
I say "somewhat symmetrical" because the difference is that data in
SponsorEvent is split between Spot and Slot.
Spot as a table has over 100 million rows (and gets larger per w

).
Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDate
just happens to be a compromise. I know what you are saying, and I agree in
principal. At some point this year the server will be upgraded from SQL
Server 7 to 2000 and maybe the optimisation might be better.

> As an aside, I recommend renaming the column [Date]. It is never a good
> idea to use reserved words as column names.
I know. Noted. We have a table called [Break] which I am loathe to change.
Granted it is a keyword, but in the industry I am in "Break" is the most
descriptive word there is for the data it contains. They really are called
"Commercial Breaks". But I will change [Date] :-)
Stephen Howe|||What, do you have a case sensitive collation? Try using att everywhere and
no Att vs. att?
This works fine for me. You might want to provide similar DDL in the future
to make it easier for others to reproduce your scenario and test their
results. (See http://www.aspfaq.com/5006)
USE Tempdb
GO
CREATE TABLE Sponsor
(
SponsorID INT,
FirstDate SMALLDATETIME
)
GO
CREATE TABLE SponsorEvent
(
SponsorID INT,
[Date] SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT Sponsor SELECT 1, NULL
INSERT Sponsor SELECT 2, NULL
INSERT Sponsor SELECT 3, NULL
INSERT Sponsor SELECT 4, NULL
INSERT SponsorEvent SELECT 1, '20010501'
INSERT SponsorEvent SELECT 1, '20010601'
INSERT SponsorEvent SELECT 1, '20040801'
INSERT SponsorEvent SELECT 3, '20040701'
INSERT SponsorEvent SELECT 3, '20010601'
INSERT SponsorEvent SELECT 4, '20030801'
GO
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor s
INNER JOIN
(
SELECT SponsorID, FirstDate = MIN([Date])
FROM SponsorEvent
GROUP BY SponsorID
) att
ON s.SponsorID = att.SponsorID
GO
SELECT * FROM Sponsor
GO
DROP TABLE SponsorEvent, Sponsor
GO
On 3/12/05 12:57 PM, in article ORy87zyJFHA.2716@.TK2MSFTNGP15.phx.gbl,
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote:

> Thanks for response
>
> No. Definitely not. What I wrote was copy and pasted out of QA.
> There is a message in colour red just before. The full message is
> Server: Msg 8155, Level 16, State 2, Line 1
> No column was specified for column 2 of 'att'.
>
> Does not work. I changed text to
> SET FirstDate = att.[Date]
> FROM Sponsor s
> and now I get
> "Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'att' does not match with a table name or alias name use
d
> in the query."
>
> Yes you are right and I would like to.
> But the reason why is that we have a analogous situation for tables where
> Sponsor and SponsorEvent which have SponsorID in common
> is reproduced with
> SpotFilm and Spot which have SpotFilmID in common
> SpotFilm and Sponsor have identical columns apart from the ID fields. It i
s
> also such that if you concatenated them, the ID's are unique, no row in
> common.
> I say "somewhat symmetrical" because the difference is that data in
> SponsorEvent is split between Spot and Slot.
> Spot as a table has over 100 million rows (and gets larger per w

).
> Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
> Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDa
te
> just happens to be a compromise. I know what you are saying, and I agree i
n
> principal. At some point this year the server will be upgraded from SQL
> Server 7 to 2000 and maybe the optimisation might be better.
>
> I know. Noted. We have a table called [Break] which I am loathe to change.
> Granted it is a keyword, but in the industry I am in "Break" is the most
> descriptive word there is for the data it contains. They really are called
> "Commercial Breaks". But I will change [Date] :-)
> Stephen Howe
>
>
>|||> Sorry, forgot the s:
>
> Should be
> FROM Sponsor s
Thanks Aaaron, done it
UPDATE Sponsor
SET FirstDate = att.[Date]
FROM
(SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att INNER JOIN Sponsor s ON Att.SponsorID=s.SPonsorID|||> What, do you have a case sensitive collation? Try using att everywhere
and
> no Att vs. att?
I don't think we do. Anything to do with SQL Server 7 not recognising the
syntax?
Perhaps SQL Server 2000 is "improved" in that order does not matter
I changed the order putting Sponsor s last, (after the SELECT .. GROUP BY)
and at that point it recognised it.
Strange
But thanks
Stephen|||Date is not just a reserved word, it is too vague to be a valid data
element name -- date of what' Is there only one sponsor, as you
showed with a singular name? If you were writing SQL instead of a
strange dialect, would this be what you meant? It is a bad practice to
name relationship tables by concatenating names together -- always ask
if the relationship has its own name. Endorsements, sponsorships, etc.
UPDATE Sponsors
SET firstdate -- of what'
= (SELECT MIN(foobar_date)
FROM Endorsements AS E
WHERE E.sponsor_id = Sponsors.sponsor_id)
You might want to read a book on SQL and see what the standard UPDATE
syntax is.
And get a book on data modeling. Names like "SponsorICodeID" make
absolutley no sense. A code is not an identifier; it is a scalar value
on a nominal scale. Most of the rest of your DDL looks like you are
writing SQL with flags, etc. -- all the classic mistakes of someone who
does not know how to make a schema.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110772877.481289.236930@.l41g2000cwc.googlegroups.com...
> It is a bad practice to name relationship tables by concatenating
names together
Mr. Celko,
Really? I've been doing a lot of that lately (sometimes M-to-M
relationships are stumpers). Is that a part of a standard I can take
a look at? Perhaps you could provide a weblink to an article or other
work with an extensive description of why it is bad, and the process
of doing better.
Sincerely,
Chris O.|||>> [name relationship tables by concatenating names together ] I've
been doing a lot of that lately (sometimes M-to-M relationships are
stumpers). <<
Not if you start from a data model. Relationships important enough to
be modeled tend to have names -- "Marriages" instead "ManWoman' or even
worse "CivilUnions" instead of "ManMan_WomanWoman_ManWomen". A
relationship name invites the attributes that go with the relationship;
Marriages implies a wedding date, license number, etc.
Google up ISO-11179; the principle is to name a thing for what it *is*,
not for what it *does* in a particular situation, not for hopw you
build it-- i.e. this is an "automobile", not a "TiresFrameMotor"; the
whole not the parts.
an extensive description of why it is bad, and the process of doing
better. <<
Look for an entire book on SQL style about the middle of this year from
me.

Complex sql update

Hi,
I'm trying to update a key in tablea with tableb using the where there
are mulitple where criteria. I'm trying to avoid the sql cursors to do
update for each record.
Is there a way I can do that in the following statement:
update billing_detail_debit_card set bdp_id =
(
select b.bdp_id from billing_detail_participant b join
billing_detail_debit_card dc
on b.billing_proc_no = dc.billing_proc_no
and b.cust_no = dc.cust_no
and b.company_no = dc.company_no
and b.participant_id = dc.participant_id
)
where billing_detail_debit_card.billing_proc_no
(
select dc.billing_proc_no, dc.cust_no, dc.company_no,
dc.participant_id
from billing_detail_participant b join billing_detail_debit_card dc
on b.billing_proc_no = dc.billing_proc_no
and b.cust_no = dc.cust_no
and b.company_no = dc.company_no
and b.participant_id = dc.participant_id
) bdp_table
= bdp_table.billing_proc_no
and billing_detail_debit_card.cust_no = bdp_table.cust_no
and billing_detail_debit_card.company_no = bdp_table.company_no
and billing_detail_debit_card.participant_id = bdp_table.participant_id--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You're working overtime. You had it, but then added too much. Try
this:
UPDATE billing_detail_debit_card
SET bdp_id =
(SELECT bdp_id
FROM billing_detail_participant b
WHERE billing_proc_no = billing_detail_debit_card.billing_proc_no
AND cust_no = billing_detail_debit_card.cust_no
AND company_no = billing_detail_debit_card.company_no
AND participant_id = billing_detail_debit_card.participant_id )
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRJITJoechKqOuFEgEQKrDgCghArCBOaCx7BJ
OfDlVboQI9nUx5AAn1nA
vGO7K01Trgrw9KG7UHn7Kw2q
=XJiT
--END PGP SIGNATURE--
dmalhotr2001@.yahoo.com wrote:
> Hi,
> I'm trying to update a key in tablea with tableb using the where there
> are mulitple where criteria. I'm trying to avoid the sql cursors to do
> update for each record.
> Is there a way I can do that in the following statement:
> update billing_detail_debit_card set bdp_id =
> (
> select b.bdp_id from billing_detail_participant b join
> billing_detail_debit_card dc
> on b.billing_proc_no = dc.billing_proc_no
> and b.cust_no = dc.cust_no
> and b.company_no = dc.company_no
> and b.participant_id = dc.participant_id
> )
> where billing_detail_debit_card.billing_proc_no
> (
> select dc.billing_proc_no, dc.cust_no, dc.company_no,
> dc.participant_id
> from billing_detail_participant b join billing_detail_debit_card dc
> on b.billing_proc_no = dc.billing_proc_no
> and b.cust_no = dc.cust_no
> and b.company_no = dc.company_no
> and b.participant_id = dc.participant_id
> ) bdp_table
> = bdp_table.billing_proc_no
> and billing_detail_debit_card.cust_no = bdp_table.cust_no
> and billing_detail_debit_card.company_no = bdp_table.company_no
> and billing_detail_debit_card.participant_id = bdp_table.participant_id
>|||Be careful with this. It updates every row of billing_detail_debit_card,
which might result in setting bdp_id to NULL for many
rows in billing_detail_debit_card you don't wish to update,
since if the subquery does not yield any rows, its value will
be NULL.
UPDATES like this can often benefit from SQL Server's
proprietary UPDATE .. FROM syntax, which might look
something like this for the query you have:
UPDATE billing_detail_debit_card
FROM billing_detail_participant AS b
WHERE b.billing_proc_no = billing_detail_debit_card.billing_proc_no
AND b.cust_no = billing_detail_debit_card.cust_no
AND b.company_no = billing_detail_debit_card.company_no
AND b.participant_id = billing_detail_debit_card.participant_id
Steve Kass
Drew University
"MGFoster" <me@.privacy.com> wrote in message
news:0rokg.6390$lf4.1883@.newsread1.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> You're working overtime. You had it, but then added too much. Try
> this:
> UPDATE billing_detail_debit_card
> SET bdp_id =
> (SELECT bdp_id
> FROM billing_detail_participant b
> WHERE billing_proc_no = billing_detail_debit_card.billing_proc_no
> AND cust_no = billing_detail_debit_card.cust_no
> AND company_no = billing_detail_debit_card.company_no
> AND participant_id = billing_detail_debit_card.participant_id )
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRJITJoechKqOuFEgEQKrDgCghArCBOaCx7BJ
OfDlVboQI9nUx5AAn1nA
> vGO7K01Trgrw9KG7UHn7Kw2q
> =XJiT
> --END PGP SIGNATURE--
>
> dmalhotr2001@.yahoo.com wrote:|||or you can add a where exists clause to update on rows where a matching row
exists.
"Steve Kass" <skass@.drew.edu> wrote in message
news:e628WzOkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> Be careful with this. It updates every row of billing_detail_debit_card,
> which might result in setting bdp_id to NULL for many
> rows in billing_detail_debit_card you don't wish to update,
> since if the subquery does not yield any rows, its value will
> be NULL.
> UPDATES like this can often benefit from SQL Server's
> proprietary UPDATE .. FROM syntax, which might look
> something like this for the query you have:
> UPDATE billing_detail_debit_card
> FROM billing_detail_participant AS b
> WHERE b.billing_proc_no = billing_detail_debit_card.billing_proc_no
> AND b.cust_no = billing_detail_debit_card.cust_no
> AND b.company_no = billing_detail_debit_card.company_no
> AND b.participant_id = billing_detail_debit_card.participant_id
> Steve Kass
> Drew University
>
> "MGFoster" <me@.privacy.com> wrote in message
> news:0rokg.6390$lf4.1883@.newsread1.news.pas.earthlink.net...
>|||Sorry, forgot the code...
update billing_detail_debit_card
set bdp_id =
(
select b.bdp_id from billing_detail_participant as b
where b.billing_proc_no = billing_detail_debit_card.billing_proc_no
and b.cust_no = billing_detail_debit_card.cust_no
and b.company_no = billing_detail_debit_card.company_no
and b.participant_id = billing_detail_debit_card.participant_id
)
where exists
(
select b.bdp_id from billing_detail_participant as b
where b.billing_proc_no = billing_detail_debit_card.billing_proc_no
and b.cust_no = billing_detail_debit_card.cust_no
and b.company_no = billing_detail_debit_card.company_no
and b.participant_id = billing_detail_debit_card.participant_id
)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:Ol9$tdUkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> or you can add a where exists clause to update on rows where a matching
row
> exists.
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:e628WzOkGHA.2200@.TK2MSFTNGP05.phx.gbl...
billing_detail_debit_card,[color=darkred
]
there
do
bdp_table.participant_id
>

Sunday, February 19, 2012

Completed(?) and urgent Triggers and Transactions question

I use the VB.NET transaction to update an sql server 2000 database. I call a number of stored procedures within this transaction.

The stored procedures will update tables. These tables use triggers..

My question is, when does the trigger get called? Is it after the each stored procedure, or is it after the whole transaction?

Jag

the trigger will execute when

- new records is being inserted into the table

- records get update

- records get deleted

from the table

|||

In my opinion triggers fire as soon as the database updated, and transaction update your table as soon as a query fires. Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks.

cheers

|||

How would you define too much?

At the moment, one of my trigger calls a stored procedure that is doing about 4 selects on a table (one with an MAX()), and maybe a 2 updates.

The stored procedure should be optimised, so it should case too much problems later (?)

EDIT: I am expecting the number of selects to increase to about 10, and the number of updates to 3. The selects will be small selects that mostly pull out ids.

Jag

|||

Hi,

The trigger will be fired immediately after the the update was executed. And the transaction will continue after trigger gets executed.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

What I was afraid of was that the triggers would not get fired during the transaction, and would be fired after the commit. But after a little testing, I found this was not true.

I am still trying to find out how much I can put into a trigger before a performance becomes an issue. I am not expecting heavy usage - I'm not even expecting moderate usage on the site. Maybe about 10 inputs/edits a day, that's it.

I have written a stored procedure and the trigger calls this if needed. The stored procedure will have upto 20 select statements to pull out values from the database, and then a couple of updates. Is this too much? I don't see it being too much as I am not expecting too much input to be going on.

Jag

|||

Just another quick question.

Same example - there is a transaction with a number of updates/inserts. The tables these apply to have triggers on then. The triggers are fired as soon as the update/insert happens, and not at the end of the transaction.

My question is, if the transaction fails and the changes are reversed, will the changes made by the triggers also be reversed. Common sense says that it should happen, but I need to ensure that it does.

Thanks in advance

Jag

Tuesday, February 14, 2012

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
JohnCompatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John

Compatibility Level

In testing an update from sql 2000 to 2005 on a junk server, I noticed that
it kept the compatibility level at 80 for the user databases and the master
database. My question is, when I update my real server, should the
campatibility lever of the master database be kept at 80 until all the user
databases are updated to 90 or can I change that right away? Also, some
vendors won't me updating their application and databases for a while yet.
Are there any gotchas for running campatibility level 80 and 90 on the same
server?
Thanks
John
Compatibility is at the DB level, so that you can control it at that level
of granularity.
When you update any DB to SQL 2005, the db is kept at '80'. You must
manually change it to '90' and this MAY affect behavior.You may have alredy
heard about Upgrade Advisor that is s FREE download from MSFT to help you
through your process. There is also another tool called Upgrade Assistant
which helps you setup a test 2000 and 2005 instance and replay a trace
against each to determine the behavior differences. This is also a FREE
downlad available at www.scalabilityexperts.com.
Rick Heiges
SQL Server MVP
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:DBC8F7AC-6C8E-4CC1-A53F-A042F7A747FC@.microsoft.com...
> In testing an update from sql 2000 to 2005 on a junk server, I noticed
> that it kept the compatibility level at 80 for the user databases and the
> master database. My question is, when I update my real server, should the
> campatibility lever of the master database be kept at 80 until all the
> user databases are updated to 90 or can I change that right away? Also,
> some vendors won't me updating their application and databases for a while
> yet. Are there any gotchas for running campatibility level 80 and 90 on
> the same server?
> Thanks
> John

Compatibility Level

Friday, February 10, 2012

Comparing two strings inside a stored procedure

Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure

For example:
String1 = "Here is some data. lets type some more data"
String2 = "Here's some data. Lets type some data here"

I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.

So I would want string2 to look like this

string2 = "Here's <strike>is</strike> some data. Lets type some <strike>more</strike> data here"

Is there an way to accomplish this inside a stored procedure?

First, you'll have to decide what algorithm determines what matches and what is different. For example, if you just compare the strings by the same position, you'll get the first part the same, the end of the first string as deleted and the end of the second string as inserted. I don't know how to implement the algorithm but I can think of some possibilities:

1. Regular expressions may have some support for this.

2. Track the user's changes character by character on the client.

3. Find a differencing algorithm / tool somewhere.

Once you've solved that, I suggest you pass the difference string to your procedure. You'll have much more flexibility and support in .NET than in SQL.

Sorry I couldn't help more. Good Luck.

|||Yes, you can. However, that is best left up to an application as it's a matter of presentation and application logic.

Thursday, March 29, 2012

concatenate problem

Thursday, March 22, 2012

computation for computed columns in sysservers

computation for computed columns in sysservers

computation for computed columns in sysservers

Sunday, March 11, 2012

Complicated Update Statement

Complicated Update query based on existing data

complicated update (for me)

Complicated question about update procedures

Thursday, March 8, 2012

Complex UPDATE

Complex sql update

Sunday, February 19, 2012

Completed(?) and urgent Triggers and Transactions question

Tuesday, February 14, 2012

Compatibility Level

Compatibility Level

Compatibility Level

Friday, February 10, 2012

Comparing two strings inside a stored procedure

Sql Comparing two tables

Blog Archive

About Me