how to ensure unique value over multiple columns

I'm looking for a way to ensure a value of a column in an insert or update is unique over multiple columns. For example, in this table
accounts
id varchar(32)
readkey varchar(16)
writekey varchar(16)
I want to ensure that when a row of accounts is inserted or updated that the union of all readkey values and writekey values contains no duplicates.

I know some "hard" ways to do this (like a second table of all keys, or a trigger that tests new readkey values against all readkey and writekey values, and likewise new writekey values against all readkey and writekey values, and so on). But I'm betting that savvy SQL folks know a better way. In case it matters, I'm using IBM DB2 8.1.

Thanks
Billcreate a composite key with unique attribute.
not sure if it will work in DB2 though|||Thanks but that doesn't do it. I'm not trying to ensure that no combination of readkey || writekey ever occurs twice. I need to ensure that no readkey is the same as any other readkey or writekey, and no other writekey is the same as any other readkey or writekey.

Bill|||I don't know DB2. In standard SQL you can create a constraint something like:

ALTER TABLE accounts a1
ADD CONSTRAINT c1
CHECK (NOT EXISTS (SELECT NULL FROM accounts a2 WHERE a2.readkey = a1.writekey));

That, along with UNIQUE constraints on the 2 columns, would do it.

Alternatively, perhaps a Materialized View based on:

SELECT 'R' AS mode, readkey AS key FROM accounts
UNION
SELECT 'W' AS mode, writekey AS key FROM accounts

... with a unique constraint on (key).|||Thanks for the suggestions. I couldn't make either work, but the ideas in them provided a way.

The CHECK constraint was my first idea, but I learned that CHECK constraints cannot depend on values from more than one row, so the SELECT which examines the whole table will not work.

The materialized view was a good idea but doesn't work, since materialized views do not permit UNION - the query has to be a subset query.

What I did get to work was to create a (regular) VIEW using the UNION roughly as you suggested, then create triggers for insert and update that throw an error if the key already exists in the union view. The view and two triggers is more complex than I hoped, but at this point I'm happy just to have a solution.

Thanks again for the help.|||In DB2 v8 you can use a sequence object for this purpose.

How to enhance the performance when there is bulk update in trigge

Dear someone who can help,
I encounter a problem where the execution of a trigger is very slow (in
terms of minute). Below is the example of the trigger that I have been
implemented.
Assume the trigger is fired on Table1 when inserting. The trigger perform
the following
- Create a temporary table of @.Temp
- Retrieve all the records from Table2 and insert into a temporary table.
- Then loop each of the temporary-Record and perform some business logic
- Update the record back into Table2
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.InsCode > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Question:
1. Is the temporary table method correctly been used? I understand that
Cursor is not recommended in Trigger.
2. How to speed up the performance.
3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
to MS Sql.
4. I have tried with call a stored procedure with explicit
transaction, but it didn't help much. Moreover, sometime this method only
able to update few rows of record in Table2 after the trigger execution.
Thank you in advance.
regards,
StephanieStephanie wrote:
> Dear someone who can help,
> I encounter a problem where the execution of a trigger is very slow
> (in terms of minute). Below is the example of the trigger that I have
> been implemented.
> Assume the trigger is fired on Table1 when inserting. The trigger
> perform the following
> - Create a temporary table of @.Temp
> - Retrieve all the records from Table2 and insert into a temporary
> table.
> - Then loop each of the temporary-Record and perform some business
> logic
> - Update the record back into Table2
> Question:
> 1. Is the temporary table method correctly been used? I understand
> that Cursor is not recommended in Trigger.
> 2. How to speed up the performance.
> 3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second)
> compare to MS Sql.
> 4. I have tried with call a stored procedure with explicit
> transaction, but it didn't help much. Moreover, sometime this method
> only able to update few rows of record in Table2 after the trigger
> execution.
> Thank you in advance.
> regards,
> Stephanie
It's considered bad practive to use cursors and any extensive processing
in triggers as they will slow down the entire transaction, keeping locks
active in the database much longer than needed. Sometimes, if the
business rules require it, you can get away with it without affecting
performance too much.
What I don't understand is why there is no reference to reference to the
inserted virtual table in the trigger. What you have is a trigger that
affects all rows in another table every time a row is updated. I think
you really need to explain to the group what you are trying to do in the
trigger and why it is necessary.
David Gugick
Imceda Software
www.imceda.com|||Dear David,
In this example, i'm trying to show that when i update record in Table1, i
need to update the 'Code' field in Table2, in this case, will update the in
bulk. May be i should correct my example as below:
========================================
==========================
CREATE TRIGGER TG_Table1_Ins ON Table1
DECLARE @.InsCode integer /* stored the Code value from Table1 – On
Inserted*/
DECLARE @.Code integer /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
Set @.InsCode = (select Code from inserted)
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3), Code integer)
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr, Code) Select KeyStr, Code From From Table2 where
Flag = 'Y'
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Code = (Select Code From #temp where KeyStr = @.KeyStr)
/*some logic processing here, this is only example*/
If (@.Code > 10)
Set @.Code = @.InsCode * 2
Else Set @.Code = @.InsCode
Update Table2 Set Code = @.Code Where KeyStr = @.KeyStr
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
==========================
Hope this is clear. Thank you.
regards,
Stephanie|||Don't use cursors or loops in a trigger. Typically it isn't a good idea
to assign column values to variables in a trigger as this normally
implies you'll have to use a loop if there is more than one row
updated.
You can handle multiple updated rows by referring the INSERTED and
DELETED tables in your trigger code. Example:
UPDATE SomeTable
SET something ...
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE Inserted.key_col = SomeTable.key_col)
Unfortunately your trigger code doesn't do anything useful so it isn't
really possible to give a solution specifc to your situation.
David Portas
SQL Server MVP
--|||On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
(snip)
>Question:
>1. Is the temporary table method correctly been used? I understand that
>Cursor is not recommended in Trigger.
Hi Stephanie,
This is still a cursor. Everytime you loop through a set of rows and
process one row as a time, you're performing a cursor operation, even if
you are not using the prebuilt cursor tools for it.
Warning against using cursors should be taken as warning against any
form of iterative processing. In SQL, you should write queries that
operate on whole sets at once.
Another thing (even though you don't ask) - a trigger fires once per
statement execution. All rows affected by the statement (be it one, zero
or five million) are in the inserted pseudo-table. The code you posted
will result in an error if there are more than one (but if you used
SELECT instead of SET, it would not error - it would just pick one of
the rows and process only that one). And it will produce wrong results
if no rows are affected (as @.InsCode will be set to NULL).

>2. How to speed up the performance.
Remove the cursor. Replace it with a set-based UPDATE statement.
You've posted two slighhtly different versions of your trigger, but I
believe that your real trigger is more complicated than this (if only
because the CREATE TRIGGER statement itself would cause an error). As an
exampl, I'll post a set-based equivalent of the second trigger you
posted:
CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
AS
UPDATE Table2
SET Code = (SELECT i.InsCode
FROM inserted AS i)
* CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
go
As you can see, the set-based code is much shorter. If you test it,
you'll find that it's much faster as well.
In case you need help rewriting your real code in set-based form, you'll
need to post the table structure (as CREATE TABLE statements), some
sample data (as INSERT statements) and expected output. Posting the slow
code you currently use might help as well. See www.aspfaq.com/5006.

>3. I have tried this in oracle, it is pretty fast (in 2 ~ 5 second) compare
>to MS Sql.
I don't know Oracle myself, but based on what I've heard, it appears as
if SQL Server is heavily optimized for set-based processing (with the
obvious tradeoff in cursor operations), whereas Oracle is either more
optimized for cursorbased operation, or a bit for both. In any case,
cursors do tend to be faster in Oracle.

>4. I have tried with call a stored procedure with explicit
>transaction, but it didn't help much. Moreover, sometime this method only
>able to update few rows of record in Table2 after the trigger execution.
Do you mean that some rows in Table2 are changed and some are not? If
the rows that need to be changed are determined by the data in the
inserted pseudo-table, then this makes sense (as only one of the rows in
inserted will be processed). But with the code you posted, this should
not be possible - it should either process all rows in table2, or none
at all - regardless of transactions in the calling stored procedure (a
trigger is always part of a transaction - if not explicit, then implicit
as part of the statement that fires the trigger). If you can post some
code to reproduce this (I'd need at least the CREATE TABLE and INSERT
statements to get the starting data, the actual trigger code and the
statement that fires the trigger), I'll gladly look into it.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

How to enhance the performance when there is bulk update in tr

Dear Hugo,
Thanks for your solution. With the set-based UPDATE method, really find that
it's much faster.
I’ll have another question. Lets take (my) the same example, now I need to
add another update trigger in Table2 as below (where the insert trigger in
Table1 still remain). In this trigger, what ever record(s) updated in Table2
will insert a new record into Table3.
========================================
=============
CREATE TRIGGER TG_Table2_Upd ON Table2
DECLARE @.Msg varchar(5) /*temporary variable */
DECLARE @.KeyStr char (3) /*temporary variable */
/*Create temporary table and data will be retrieved from table 2*/
Create Table #Temp (KeyStr char (3))
/* Assume the table2 contains 3 thousand records with 30 data fields*/
Insert into #Temp (KeyStr) Select KeyStr From inserted
/*loop for this temporary table*/
While Exists (Select * From #Temp) Begin
Set @.KeyStr = (Select top 1 KeyStr From #Temp)
Set @.Msg = ‘U’ + @.KeyStr
Insert into Table3 (UpdateTime, Message) values (CURRENT_TIMESTAMP, @.Msg)
Delete #Temp Where KeyStr = @.KeyStr
End
Drop table #Temp
End
========================================
=============
In this case, how can I improve the performance?
regards,
Stephanie
"Hugo Kornelis" wrote:

> On Wed, 16 Mar 2005 18:35:02 -0800, Stephanie wrote:
> (snip)
> Hi Stephanie,
> This is still a cursor. Everytime you loop through a set of rows and
> process one row as a time, you're performing a cursor operation, even if
> you are not using the prebuilt cursor tools for it.
> Warning against using cursors should be taken as warning against any
> form of iterative processing. In SQL, you should write queries that
> operate on whole sets at once.
> Another thing (even though you don't ask) - a trigger fires once per
> statement execution. All rows affected by the statement (be it one, zero
> or five million) are in the inserted pseudo-table. The code you posted
> will result in an error if there are more than one (but if you used
> SELECT instead of SET, it would not error - it would just pick one of
> the rows and process only that one). And it will produce wrong results
> if no rows are affected (as @.InsCode will be set to NULL).
>
> Remove the cursor. Replace it with a set-based UPDATE statement.
> You've posted two slighhtly different versions of your trigger, but I
> believe that your real trigger is more complicated than this (if only
> because the CREATE TRIGGER statement itself would cause an error). As an
> exampl, I'll post a set-based equivalent of the second trigger you
> posted:
> CREATE TRIGGER TG_Table1_Ins ON Table1 AFTER INSERT
> AS
> UPDATE Table2
> SET Code = (SELECT i.InsCode
> FROM inserted AS i)
> * CASE WHEN Table2.Code > 10 THEN 2 ELSE 1 END
> go
> As you can see, the set-based code is much shorter. If you test it,
> you'll find that it's much faster as well.
> In case you need help rewriting your real code in set-based form, you'll
> need to post the table structure (as CREATE TABLE statements), some
> sample data (as INSERT statements) and expected output. Posting the slow
> code you currently use might help as well. See www.aspfaq.com/5006.
>
> I don't know Oracle myself, but based on what I've heard, it appears as
> if SQL Server is heavily optimized for set-based processing (with the
> obvious tradeoff in cursor operations), whereas Oracle is either more
> optimized for cursorbased operation, or a bit for both. In any case,
> cursors do tend to be faster in Oracle.
>
> Do you mean that some rows in Table2 are changed and some are not? If
> the rows that need to be changed are determined by the data in the
> inserted pseudo-table, then this makes sense (as only one of the rows in
> inserted will be processed). But with the code you posted, this should
> not be possible - it should either process all rows in table2, or none
> at all - regardless of transactions in the calling stored procedure (a
> trigger is always part of a transaction - if not explicit, then implicit
> as part of the statement that fires the trigger). If you can post some
> code to reproduce this (I'd need at least the CREATE TABLE and INSERT
> statements to get the starting data, the actual trigger code and the
> statement that fires the trigger), I'll gladly look into it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:

>Dear Hugo,
>Thanks for your solution. With the set-based UPDATE method, really find tha
t
>it's much faster.
>Ill have another question. Lets take (my) the same example, now I need to
>add another update trigger in Table2 as below (where the insert trigger in
>Table1 still remain). In this trigger, what ever record(s) updated in Table
2
>will insert a new record into Table3.
(snip)
Hi Stephanie,
The trigger code you posted will never work. You have omitted a part of
the CREATE TRIGGER statement, and you have an unmatched END.
Anyway, instead of filling a temp table, iterating iver the rows and
inserting new rows one at a time in Table3, you can easily do this in
one statement:
CREATE TRIGGER TG_Table2_Upd
ON Table2
AFTER INSERT, UPDATE -- Wild guess
AS
INSERT INTO Table3 (UpdateTime, Message)
SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
FROM inserted
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much! :)
regards,
Stephanie Kan
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 21:45:02 -0800, Stephanie wrote:
>
> (snip)
> Hi Stephanie,
> The trigger code you posted will never work. You have omitted a part of
> the CREATE TRIGGER statement, and you have an unmatched END.
> Anyway, instead of filling a temp table, iterating iver the rows and
> inserting new rows one at a time in Table3, you can easily do this in
> one statement:
> CREATE TRIGGER TG_Table2_Upd
> ON Table2
> AFTER INSERT, UPDATE -- Wild guess
> AS
> INSERT INTO Table3 (UpdateTime, Message)
> SELECT CURRENT_TIMESTAMP, 'U' + KeyStr
> FROM inserted
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

how to enforce precedence at data flow level?

I wish to use the same data to update 2 different tables.

There is no green arrow output from the OLE DB data destination, so I can't have another component following on from the first insert.

This means I have to use the Multicast to 'copy' the data prior to the first table insert.

I can then use the data to perform inserts to both tables.

However, there is an FK constraint between these two tables, so I need to wait until the first table insert has finished before performing the second table insert.

How can I do this? How can I make the second insert dependent on the first?

Hi ya,

Unfortunately there is no precedence at data flow level. Your best bet would be to put it in 2 data flows and make sure that the first data flow is on success.

If you do want to run it in a transaction then put a sequence container and put the transaction property for container as Required whille both data flows should be supported.

Sorry but i couldn't find anything else........ probably the big guys would answer if there is anything else?

Hope that helps

Cheers

Rizwan

|||That's a pain.

That means i'll have to build the dataset up all over again......and no i daren't cut and paste due to the endless xml/serialization errors I always/randomly seem to get that completely blow up the IDE

I'm surprised there is no 'rendezvous' component in the toolbox.

do you know if anyone has written a custom component like this?|||

Sure this is but then again you always have the option to select a custom script task and then programatically do the importing and saving of data........ though this is just an idea as i haven't tried it myself yet.

leave the thread open and we'll see if Moderators or MVPs has something else in their mind.

Cheers

Rizwan

|||

One way to do this would to have the DataFlow drop the data for the child table to a RAW file, then in a 2nd dataflow read the RAW and piopulate the table|||

I agree that not having precedent constraints at the data flow level is a nuisance; DataStage used to allow it. Sorry, I am not offerring any useful advice here but want to let you know I share your pain as I had to recently do the same.

desibull

|||

desibull wrote:

I agree that not having precedent constraints at the data flow level is a nuisance; DataStage used to allow it. Sorry, I am not offerring any useful advice here but want to let you know I share your pain as I had to recently do the same.

desibull

I just want to let my opinion be known on this topic:

There is no reason to have precedence constraints in a data flow. A data flow is designed to move data in buffers -- as fast as it can. To be able to enforce ordering is non-sense. Just break the data up into two or more data flows. I do not support the notion of precedence constraints in a data flow.|||

I agree with Paul - the easiest way to do this without having to reprocess the dataset is to drop it to a RAW destination. It's also extremely fast.

|||

Hi,

That means i was right. Great

Cheers

Rizwan

how to enforce a trigger when update for each record when updates several records bulky?

I made a trigger on a table that fires when update happens, the trigger fires when attempting to update a single record (that is normally) but when trying to update several records bulky using one update statement it fires only once either.

My question is, how to enforce firing the trigger for each record when updates bulky? i.e. how to ensure that when I use the following update statement

UPDATE MyTableName SET ColumnName = 5

And there are 10 records that affected; that the trigger would fire 10 times? (I have the fact that it fires only once)

In SQL server, a trigger fires once per statement, not per row and this can not be changed. You must write your trigger to be able to handle a multiple row update. Post your trigger code and likely someone here can help you re-write it to work for multiple row updates.|||Thanks David, your reply was helpfulsql

How to enable the SQL to update all fields?

How to enable the SQL to update all fields?

"UPDATE addresses SET "

+ "strCompany='" + strCompany + "',"

+ "Name='" + strName + "',"

+ "strAddress='" + strAddress + "',"

+ " strPhone='" + fields.phone.getText() +
"',"

+ " strHp='" + fields.hp.getText() + "',"

+ " strFax='" + fields.fax.getText() +
"',"

+ " strEmail='" + strEmail + "',"

+ " strStart='" +
fields.start.getText().trim() +
"',"

+ " intDay= '" + Integer.valueOf(dd) +
"',"

+ " intMonth='" + Integer.valueOf(mm) +
"',"

+ " intYear='" + Integer.valueOf(yy) +
"',"

+ " strMrc='" + fields.mrc.getText() +
"',"

+ " strIsp='" + fields.isp.getText() +
"',"

+ " strDes='" + fields.des.getText() +
"',"

+ " strSale='" + fields.sale.getText() +
"',"

+ " strContract='" +
fields.contract.getText() +
"',"

+ " strMark='" + fields.mark.getText() +
"'"

+ " WHERE strCompany='" + strCompany

+ "'"
+ "AND strName='" + strName + "'";

You can check out more about the UPDATE statement here:

http://msdn2.microsoft.com/en-us/library/ms177523.aspx

There are a few examples at the bottom of the page. Just paste those into Management Studio and run them.

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
Sren
Hi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SørenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
--
HTH
Kalen Delaney, SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>sql

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SrenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-
28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

How to edit long text data in SQL Server

Hi Guys,

I have editing a SQL Server table field that have long text data. I am
updating some text in this field. How can I update this field instead
of re-write all text. With the Select command its gives me complete
text in one line and it hard to read it. Any idea. Thanks in Advance

Adnanharoonqureshi@.sympatico.ca (Adnan Jamil) wrote in message news:<6fbfa40b.0310290829.334c8bd6@.posting.google.com>...
> Hi Guys,
> I have editing a SQL Server table field that have long text data. I am
> updating some text in this field. How can I update this field instead
> of re-write all text. With the Select command its gives me complete
> text in one line and it hard to read it. Any idea. Thanks in Advance
> Adnan

I don't really understand what your issue is - perhaps you can give
some more information? Are you trying to replace part of a string? If
so, you can use UPDATETEXT for text columns, or REPLACE() for
char/varchar columns.

Simon

how to dump or append data to a text file

i am doing some insert or update on my db, and i want to dump the errors, if any, to a text file.

If the text file deosnt exist, then i want to create it. Then append data to it, in smthg like:

insert into mytable values (f1,f2,f3)

IF @.@.ERROR <> 0

begin

-- check if text file exist

-- if no create it and write 'an error occured etc...'

-- else append to it another line 'an error occured etc...

end

thanks for helping!!

Hi Terry,

If you're using 2000, then there really is no way to achieve what you are asking except to shell the command out via xp_cmdshell - which will do everything you asked with a little "creativity".

If using 2005, then your best bet would be to wrap the required functionality in a CLR stored proc. Again, BOL has some great info on this.

Cheers,
Rob

how to do update of select columns based on...

the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;

these are all numeric data types.Why are you moving columns? Here's a hint on how to do it. Go look up CASE expressions in your manual.

SELECT CASE WHEN column4 < 0 THEN column4 + column3 ELSE 0 END As 'An Example'
FROM MyTable|||There are many ways to accomplish what you've specified, but the simplest way is for you to code the steps as you've described them... That will be the easiest for you to understand going forward because it is how you think about the operations involved.

If you are looking for one of the other ways to go about this, you'll have to explain what you want in a bit more detail. If this is what you want, I'd describe the problem in terms of the real world instead of in terms of the columns that exist in your database now... There may be a much better way to get the same answer!

-PatP|||i have 5 columns that represent aged balances.
0 thru 4
the first part about with checking the values to 0 is due to some bad data since a - negative balance should not be aged i want to roll it down to column0 in order for the age to be current
then after that is done i want to really age the balances forward 1 column each. the first column 0 will be the new current amount due so if it is negative i want to move 0 to my next column1. if it is not negative then i want to add column0 to column1 and move 0 to column0 so then when i go thru another process i add the new billing amount to the column0 (current due)|||is there a better way? (i am way to un-educated on sql syntax/options)

update ACCTF_TEST
set a_curr = case when a_120 < 0 then a_120 + a_curr else a_curr end,
a_120 = case when a_120 < 0 then 0 else a_120 end
where a_lastage <> 20060402
go
update ACCTF_TEST
set a_curr = case when a_90 < 0 then a_90 + a_curr else a_curr end,
a_90 = case when a_90 < 0 then 0 else a_90 end
where a_lastage <> 20060402
go
update ACCTF_TEST
set a_curr = case when a_60 < 0 then a_60 + a_curr else a_curr end,
a_60 = case when a_60 < 0 then 0 else a_60 end
where a_lastage <> 20060402
go
update ACCTF_TEST
set a_curr = case when a_30 < 0 then a_30 + a_curr else a_curr end,
a_30 = case when a_30 < 0 then 0 else a_30 end
where a_lastage <> 20060402
go
update ACCTF_TEST
set a_120 = a_120 + a_90,
a_90 = 0
where a_lastage <> 20060402
go
update ACCTF_TEST
set a_90 = a_90 + a_60,
a_60 = 0
where a_lastage <> 20060402
go
update ACCTF_TEST
set a_60 = a_60 + a_30,
a_30 = 0
where a_lastage <> 20060402
go
update ACCTF_TEST
set a_30 = case when a_curr > 0 then a_curr else 0 end,
a_curr = case when a_curr > 0 then 0 else a_curr end
where a_lastage <> 20060402|||Relational databases aren't spreadsheets.

Could you explain the BUSINESS purpose of what you're doing as opposed to how you think the technical process should occur? It sounds a lot like you're trying to use MSSQL like it was an excel spreadsheet, that's going to bite you if true.|||1) original files were non-sql (proprietery format - flat files)
original applications COBOL code
2) customers wanted sql file system for our current apps (COBOL-Acucorp)
3) acucorp announces sql compliance via ntwdblib.dll (little/no change required)
4) we implimented said compliance
5) complaints about slowness
6) optimized code to take advantage of where constraint when possible
7) still complaints about slowness
8) create stored proceedures to do some of the actual COBOL programs to bypass the ntwdblib
9) i get assigned this task - reduce time to AGE accounts and to reset the current balance to 0 for the UPDATE process to load it.
10) yes we are in process of writing a non-ntwdblib binding app(strickly sql code)
11) its about 2 yrs out
12) complaints still coming in...|||Bear in mind that I'm not 100% sure of how you want to present the aging, but what I would use to replace all of your code would look something a lot like this:UPDATE ACCTF_TEST
SET a_curr = a_curr
+ CASE WHEN a_30 < 0 THEN a_30 ELSE 0 END
+ CASE WHEN a_60 < 0 THEN a_60 ELSE 0 END
+ CASE WHEN a_90 < 0 THEN a_90 ELSE 0 END
+ CASE WHEN a_120 < 0 THEN a_120 ELSE 0 END
, a_30 = CASE WHEN a_curr < 0 THEN 0 ELSE a_curr END
, a_60 = CASE WHEN a_30 < 0 THEN 0 ELSE a_30 END
, a_90 = CASE WHEN a_60 < 0 THEN 0 ELSE a_60 END
, a_120 = CASE WHEN a_90 < 0 THEN 0 ELSE a_90 END + a_120 -- To acheive "bucket brigade"
WHERE a_lastage <> 20060402-PatP

How to do this update?

Greetings,

In the customers table in Northwind db, one can update PK
(customerid) and all other fields in the same table. My question is
how can you do this in the udpate stat. That is, if one wants to
write update query to update all fields including PK, how it can be
set? Using PK in the SET statement, gives an error, because this field
might have changed during the update?

MTIA,
Grawshaal (grawsha2000@.yahoo.com) writes:
> In the customers table in Northwind db, one can update PK
> (customerid) and all other fields in the same table. My question is
> how can you do this in the udpate stat. That is, if one wants to
> write update query to update all fields including PK, how it can be
> set? Using PK in the SET statement, gives an error, because this field
> might have changed during the update?

Updating the PK does not have to be a problem:

CREATE TABLE x (a int NOT NULL PRIMARY KEY,
b varchar(23) NOT NULL)
go
INSERT x VALUES( 1, 'KJK')
INSERT x VALUES( 2, 'NJJDGF')
go
UPDATE x
SET a = 10,
b = 'Hall!'
WHERE a = 1
go
SELECT * FROM x

However, this fails:

UPDATE Northwind..Customers
SET CustomerID = 'KKKKK'
WHERE CustomerID = 'ALFKI'

And the error message tells us why:

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint
'FK_Orders_Customers'. The conflict occurred in database 'Northwind',
table 'Orders', column 'CustomerID'.
The statement has been terminated.

Since there is a reference to the table, you cannot change the id
of a customer that has orders. If you added a new customer to the table,
you could easily change its ID, as you long as you don't add orders for
it.

One way to handle this, is to change the foreign-key defintion to say
ON UPDATE CASCADE, in which case the change would be propagated to
Orders.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946D7C7374FAYazorman@.127.0.0.1>...
> al (grawsha2000@.yahoo.com) writes:
> > In the customers table in Northwind db, one can update PK
> > (customerid) and all other fields in the same table. My question is
> > how can you do this in the udpate stat. That is, if one wants to
> > write update query to update all fields including PK, how it can be
> > set? Using PK in the SET statement, gives an error, because this field
> > might have changed during the update?
> Updating the PK does not have to be a problem:
> CREATE TABLE x (a int NOT NULL PRIMARY KEY,
> b varchar(23) NOT NULL)
> go
> INSERT x VALUES( 1, 'KJK')
> INSERT x VALUES( 2, 'NJJDGF')
> go
> UPDATE x
> SET a = 10,
> b = 'Hall!'
> WHERE a = 1
> go
> SELECT * FROM x
> However, this fails:
> UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'
> And the error message tells us why:
> Server: Msg 547, Level 16, State 1, Line 1
> UPDATE statement conflicted with COLUMN REFERENCE constraint
> 'FK_Orders_Customers'. The conflict occurred in database 'Northwind',
> table 'Orders', column 'CustomerID'.
> The statement has been terminated.
> Since there is a reference to the table, you cannot change the id
> of a customer that has orders. If you added a new customer to the table,
> you could easily change its ID, as you long as you don't add orders for
> it.
> One way to handle this, is to change the foreign-key defintion to say
> ON UPDATE CASCADE, in which case the change would be propagated to
> Orders.

I don't have a problem with this. I did ticked the CascadeOnUpdate.
The problem is, the update woun't happen becuase there will be a
Concurency Violation. Try to do this(with all cascades, and still you
will recieve an err)
UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'|||> UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'

This UPDATE works for me once I've enabled Cascading updates on the child
tables (CustomerCustomerDemo and Orders). Exactly what error message are you
getting? Maybe you already have a row where CustomerID = 'KKKKK' so this
violates the primary key?

--
David Portas
----
Please reply only to the newsgroup
--|||al (grawsha2000@.yahoo.com) writes:
> I don't have a problem with this. I did ticked the CascadeOnUpdate.
> The problem is, the update woun't happen becuase there will be a
> Concurency Violation. Try to do this(with all cascades, and still you
> will recieve an err)
> UPDATE Northwind..Customers
> SET CustomerID = 'KKKKK'
> WHERE CustomerID = 'ALFKI'

Since I am lazy I did not even try this. I know that it does not produce
any error with with the appropriate cascade. Least of all concurrency
violation, because SQL Server does not produce any such errors.

However, some client tools and libraries are doing smart things behind
your back, and may be outsmarted by your manoevre.

So you need to tell us in which context you get the error message (as
well as of course the exact text of the error message). I have a strong
feeling that you are not submitting the above from Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946DA7996D537Yazorman@.127.0.0.1>...
> al (grawsha2000@.yahoo.com) writes:
> > I don't have a problem with this. I did ticked the CascadeOnUpdate.
> > The problem is, the update woun't happen becuase there will be a
> > Concurency Violation. Try to do this(with all cascades, and still you
> > will recieve an err)
> > UPDATE Northwind..Customers
> > SET CustomerID = 'KKKKK'
> > WHERE CustomerID = 'ALFKI'
> Since I am lazy I did not even try this. I know that it does not produce
> any error with with the appropriate cascade. Least of all concurrency
> violation, because SQL Server does not produce any such errors.
> However, some client tools and libraries are doing smart things behind
> your back, and may be outsmarted by your manoevre.
> So you need to tell us in which context you get the error message (as
> well as of course the exact text of the error message). I have a strong
> feeling that you are not submitting the above from Query Analyzer.

You are right! I'm doing this from VB.NET. But since this is not the
group for such post and since I have found out about this late, how
can I fix this? I gusse I need to submit the original value+the
changed value..may be??|||al (grawsha2000@.yahoo.com) writes:
> You are right! I'm doing this from VB.NET. But since this is not the
> group for such post and since I have found out about this late, how
> can I fix this? I gusse I need to submit the original value+the
> changed value..may be??

I'm still a learner of ADO .Net, so maybe I am not the one to give
expert advice. But even as an expert, I would have problems without
your code at hand.

The answer to your question may be in David Sceppa's book on ADO .Net
which lies next to me on the table. I don't find anything on a quick
look, though. But it's a good book.

Being an SQL person, I would probably define my own UpdateCommand
for the DataAdapter, but there may be better support build into
ADO .Net. If you find some ADO .Net group, you might get better
answers there.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to do row level locking in SQL server 2005?

Hi,
For example, there are 2 users connecting to the same table and want to
update the different rows. They are requested to use the row level locking.
Pls advise and provide the example of sql statement or sample code.
Thank you...for example, as below..
Trans 1 :
UPDATE YourTable WITH (ROWLOCK)
SET YourCol = 'Value'
WHERE ID = 1
Trans 2:
UPDATE YourTable WITH (ROWLOCK)
SET YourCol = 'Value'
WHERE ID = 2
Whats happen is that the Trans 2 will get waiting ...& hanging there until
..the Trans 1 release or commit...
My problem here is ..how could i get a message that ...when the trans 1 is
row lock (updating), the trans 2 will get the message and diplay the allert
to the user.
Thanks
"Connie" <yfchan@.kdu.edu.my> wrote in message
news:uXKVV2vpGHA.2292@.TK2MSFTNGP05.phx.gbl...
> Hi,
> For example, there are 2 users connecting to the same table and want to
> update the different rows. They are requested to use the row level
> locking.
> Pls advise and provide the example of sql statement or sample code.
> Thank you
>
>|||There are several answers to this one on the SQL Server Programming group.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Connie" wrote:
> Hi,
> For example, there are 2 users connecting to the same table and want to
> update the different rows. They are requested to use the row level locking.
> Pls advise and provide the example of sql statement or sample code.
> Thank you
>
>|||You might find this article useful.
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Connie" <yfchan@.kdu.edu.my> wrote in message
news:uXKVV2vpGHA.2292@.TK2MSFTNGP05.phx.gbl...
> Hi,
> For example, there are 2 users connecting to the same table and want to
> update the different rows. They are requested to use the row level
> locking.
> Pls advise and provide the example of sql statement or sample code.
> Thank you
>
>

How to do REPLACE in SQL Queries

Hi,

I am having a situation where I need to update a column in my SQL table that contains a link to an image file. Basically ...

I have this stored in a column IMAGESRC

Project/aa11be5d-dd9e-48c8-9d8c-6a972e996b28/ProjectImages/702d_2.jpg

I need to change it to this

Project/NEWUSERID/ProjectImages/702d_2.jpg

How can I accomplish this in SQL?

thanks in Advance

Dollarjunkie

http://msdn2.microsoft.com/en-us/library/ms186862.aspx

http://www.sqlteam.com/article/using-replace-in-an-update-statement

Update table set IMAGESRC = Replace(IMAGESRC, 'aa11be5d-dd9e-48c8-9d8c-6a972e996b28', 'NEWUSERID')

|||

Hello Dollarjunkie,

In SQL you have two functions you can use:

- CharIndex to locate the position of the first and second '/'

- Substring to split your string into parts that you can concatenate to a new string.

In your case it will be something like:

... Substring(IMAGESRC, 1, CharIndex(IMAGESRC, '/', 1) + 1) + NEWUSRID +

Substring(IMAGESRC, CharIndex(IMAGESRC, '/', CharIndex(IMAGESRC, '/', 1) + 1))

The first substring results in: Project/

The second substring results in: /ProjectImages/702d_2.jpg

Hope this helps.

Jeroen Molenaar.

how to do muliplication within UPDATE ?

Hi

I have a product table containing price. I want to update every price in a table by 2.5.

Unfortunately, the price field is actually a varchar(20) and I am not allowed to change it into int because this is a legacy program and the project leads forbid to make change to the database

I did a user-defined scalar function to convert string into int and try the following update statement but all it does is to change 0.0 into 0 .

UPDATE Product_Info SET Price = str(dbo.cval(Price)*2)

Any idea please?

***Before***

9> select price from product_info
10> go
price
--
0.00
0.00
0.00
0.00
1
0.00
0.00
9

**1_pri
**2_pri
**3_pri

*** After ****

1> select price from product_info
2> go
price
--
0
0
0
0
1
0
0
9

0
0
0

The function:

-
User-Defined Scalar Function
-

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: 7 Nov 2006
-- Description: Convert String into Int
-- =============================================
CREATE FUNCTION [dbo].[CVal]
(
-- Add the parameters for the function here
@.input varchar(10)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
--DECLARE @.val VARCHAR(12)
--DECLARE @.return INT
--SET @.val = @.input

--SET @.return = SELECT CONVERT(INT, LEFT(@.val,PATINDEX('%[^0-9]%',@.val+' ')-1))

RETURN CONVERT(INT, LEFT(@.input,PATINDEX('%[^0-9]%',@.input+' ')-1))

END

incerese by 2.5 or 2.5 times !!!

update Product_Info set price = (cast(price as float) + 2.5)

or if 2.5 times id wat u want

update Product_Info set price = (cast(price as float) * 2.5)

|||

wow....that is amazing

that works perfectly

Thank you

How to do an update only if it will not be blocked by a lock ?

Hi,
I've got a stored proc called concurrently with different parameters.
In this proc, I would like to update a row of a statistic table but ONLY if
the update statement will not be blocked by a lock.
Is there any way to achieve this in SQL2000 ?Hi
> In this proc, I would like to update a row of a statistic table but ONLY i
f
> the update statement will not be blocked by a lock.
When one resource is blocked then you can't any way update except wait for
the resourse.If you dont want to wait for the resource then you can terminat
e
it through code. well you can set Lockout time and also check whether the
sproc is taking more time than that so that you can simply log the error
instead of waiting.
I f it is a deadlock then sql server returns Error: 1204 which you can catch
in @.@.error and take procedure to logical end.
If I understand you correctly, you want to avoid contention on a resourse so
that others can have access to the table.
We can sugget better answer only when we know fully what's problem is. Post
detailed problem
--
Regards
R.D
--Knowledge gets doubled when shared
"SoftLion" wrote:

> Hi,
> I've got a stored proc called concurrently with different parameters.
> In this proc, I would like to update a row of a statistic table but ONLY i
f
> the update statement will not be blocked by a lock.
> Is there any way to achieve this in SQL2000 ?
>
>|||Ok I've done this (we are inside a transaction):
SET XACT_ABORT OFF
SET LOCK_TIMEOUT 0
UPDATE MyTable WITH (ROWLOCK) SET ...
SET LOCK_TIMEOUT -1
SET XACT_ABORT ON
And it seems to work.
The only thing, I got an error in the query analyser when the update has
been aborted, but I think it can be safely ignored.

How to do an update on existing records?

I have one table of new records (tableA) that may already exist in
tableB. I want to insert these records into tableB with insert if they
don't already exist, or update any existing ones with new data if they
do already exist. A column (Action) in tableA already tells me whether
this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
an insert with

select * into tableB from tableA where Action = 'INSERT'

...and I think I can handle the delete.

But I'm stuck on the update. How do I do the update? An ordinary
UPDATE statement just won't do unless I use a cursor to cycle through
the recordset. I want to avoid a cursor."Google Mike" <googlemike@.hotpop.com> wrote in message
news:25d8d6a8.0402231212.16ab7593@.posting.google.c om...
> I have one table of new records (tableA) that may already exist in
> tableB. I want to insert these records into tableB with insert if they
> don't already exist, or update any existing ones with new data if they
> do already exist. A column (Action) in tableA already tells me whether
> this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
> an insert with
> select * into tableB from tableA where Action = 'INSERT'
> ...and I think I can handle the delete.
> But I'm stuck on the update. How do I do the update? An ordinary
> UPDATE statement just won't do unless I use a cursor to cycle through
> the recordset. I want to avoid a cursor.

I don't completely understand your description, and it would be useful to
see the structure of your tables (ie CREATE TABLE statements), as well as
some sample data. However, here is a fairly generic solution - if it doesn't
work as you expect, then please consider posting the additional information.

/* INSERT new records */

insert into dbo.tableB (col1, col2, ...)
select col1, col2, ...
from dbo.tableA a
where not exists
(select * from dbo.tableB b
where a.PrimaryKeyCol = b.PrimaryKeyCol)
and a.[Action] = 'INSERT'

/* UPDATE existing records */

update dbo.tableB
set col1 = a.col1, col2 = a.col2, ...
from dbo.tableB b
join dbo.tableA a
on a.PrimaryKeyCol = b.PrimaryKeyCol
where a.[Action] = 'UPDATE'

/* DELETE existing records */

delete from dbo.tableB
where exists
(select * from dbo.tableA a
where a.PrimaryKeyCol = dbo.tableB.PrimaryKeyCol
and a.[Action] = 'DELETE')

Note that 'Action' is listed in "Reserved Keywords" as a word to avoid using
in code (at least in SQL 2000 Books Online - you didn't mention which
version of MSSQL you're using).

Simon