Hello, everyone
I need to create a trigger to update a FLAG column by row modification. The table likes,
Col1 FLAG
a 0
b 0
c 0
d 0
e 0
If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.
Anyhelp will be approciated.
Thanks a lot
ZYTIf col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?
-PatP|||Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:
Update YourTable
set FLAG = 1
from YourTable
inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
where YourTable.Col1 <> Deleted.Col1|||So what happens when the code that launches the trigger is something like:UPDATE yourtable SET col1 = 'a'All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!
-PatP|||All of the rows except for one change.-PatP
I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.
But yes, as I mentioned in my post, he is going to need a primary key on the table.
Showing posts with label likes. Show all posts
Showing posts with label likes. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Need a help on a SQL Query
Could somebody please help me on following SQL Query.
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.Try:
select
TestType
, count (*)
from
MyTable
group by
TestType
order by
TestType
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<wubin_98@.yahoo.com> wrote in message
news:1140653005.105032.132270@.g44g2000cwa.googlegroups.com...
Could somebody please help me on following SQL Query.
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.|||If I understand correctly, you only want the last test passed for each
SerialNumber to be counted.
SELECT LastPassed, count(SerialNumber)
FROM (select SerialNumber,
max(PassedTestType) as LastPassed
from TestHistory
group by SerialNumber) as T
GROUP BY LastPassed
ORDER BY LastPassed
Roy
On 22 Feb 2006 16:03:25 -0800, "wubin_98@.yahoo.com"
<wubin_98@.yahoo.com> wrote:
>Could somebody please help me on following SQL Query.
>Single table data likes this:
>SerialNumber PassedTestType
>20001 1
>20001 2
>20001 3
>20001 4
>20001 5
>20002 1
>20002 2
>20002 3
>20003 1
>20003 2
>20004 1
>.............
>.............
>There are total 5 tests (1-5 indicate each test). A new record will be
>added in when it passed a test. Unit always start from test 1 to test 5
>I want to create a simple report like this:
>TestType Total number of units in this test stage
>1 2356
>2 456
>3 4352
>4 176
>5 36253
>How to write a SQL query like above Work In Process Report ?
>Thanks.|||Roy,
It is just what I need.
Thank you very much!
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.Try:
select
TestType
, count (*)
from
MyTable
group by
TestType
order by
TestType
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<wubin_98@.yahoo.com> wrote in message
news:1140653005.105032.132270@.g44g2000cwa.googlegroups.com...
Could somebody please help me on following SQL Query.
Single table data likes this:
SerialNumber PassedTestType
20001 1
20001 2
20001 3
20001 4
20001 5
20002 1
20002 2
20002 3
20003 1
20003 2
20004 1
.............
.............
There are total 5 tests (1-5 indicate each test). A new record will be
added in when it passed a test. Unit always start from test 1 to test 5
I want to create a simple report like this:
TestType Total number of units in this test stage
1 2356
2 456
3 4352
4 176
5 36253
How to write a SQL query like above Work In Process Report ?
Thanks.|||If I understand correctly, you only want the last test passed for each
SerialNumber to be counted.
SELECT LastPassed, count(SerialNumber)
FROM (select SerialNumber,
max(PassedTestType) as LastPassed
from TestHistory
group by SerialNumber) as T
GROUP BY LastPassed
ORDER BY LastPassed
Roy
On 22 Feb 2006 16:03:25 -0800, "wubin_98@.yahoo.com"
<wubin_98@.yahoo.com> wrote:
>Could somebody please help me on following SQL Query.
>Single table data likes this:
>SerialNumber PassedTestType
>20001 1
>20001 2
>20001 3
>20001 4
>20001 5
>20002 1
>20002 2
>20002 3
>20003 1
>20003 2
>20004 1
>.............
>.............
>There are total 5 tests (1-5 indicate each test). A new record will be
>added in when it passed a test. Unit always start from test 1 to test 5
>I want to create a simple report like this:
>TestType Total number of units in this test stage
>1 2356
>2 456
>3 4352
>4 176
>5 36253
>How to write a SQL query like above Work In Process Report ?
>Thanks.|||Roy,
It is just what I need.
Thank you very much!
Subscribe to:
Posts (Atom)