MS SQL Server - Update table if condition is met?

Author

Update table if condition is met?

aljamala@gmail.com

2006-10-24, 6:37 pm

Hello,

I am having difficulty finding a way to convert this code into a stored
procedure on SQL Server...Here is the original code

IF Request("updflag" & n) = "Y" then

cmdTemp.CommandText = " SELECT ast.asset_id, " & _
" mast.meter_read_due_dt, mast.meter_sort,
mast.update_flag " & _
" From asset ast, meter_asset mast" & _
" WHERE (ast.asset_id='" & Request("assetcode"
& n) & "')" & _
" AND (ast.asset_id = mast.asset_id)"

cmdTemp.CommandType = adCmdText 'Text
Command
Set cmdTemp.ActiveConnection = BEConn 'Set database connection
objRS.Open cmdTemp, , adOpenKeyset, adLockOptimistic
If Not objRS.EOF and Not objRS.BOF then
sAssetCD = objRS.Fields("asset_id").value
dtMeterDate = objRS.Fields("meter_read_due_dt")
objRS.Fields("meter_sort") = Trim(Request("newsort" & n))
objRs.Fields("update_flag") = "Y"
objRS.Update

Now after the SELECT is executed I want to check if any records were
returned and if so, go ahead with the update just like the code above
does. Any ideas on how to do this?

Thanks

David Portas

2006-10-24, 6:37 pm

<aljamala@gmail.com> wrote in message
news:1160682626.526556.96360@m73g2000cwd.googlegroups.com...
> Hello,
>
> I am having difficulty finding a way to convert this code into a stored
> procedure on SQL Server...Here is the original code
>
> IF Request("updflag" & n) = "Y" then
>
> cmdTemp.CommandText = " SELECT ast.asset_id, " & _
> " mast.meter_read_due_dt, mast.meter_sort,
> mast.update_flag " & _
> " From asset ast, meter_asset mast" & _
> " WHERE (ast.asset_id='" & Request("assetcode"
> & n) & "')" & _
> " AND (ast.asset_id = mast.asset_id)"
>
> cmdTemp.CommandType = adCmdText 'Text Command
> Set cmdTemp.ActiveConnection = BEConn 'Set database connection
> objRS.Open cmdTemp, , adOpenKeyset, adLockOptimistic
> If Not objRS.EOF and Not objRS.BOF then
> sAssetCD = objRS.Fields("asset_id").value
> dtMeterDate = objRS.Fields("meter_read_due_dt")
> objRS.Fields("meter_sort") = Trim(Request("newsort" & n))
> objRs.Fields("update_flag") = "Y"
> objRS.Update
>
> Now after the SELECT is executed I want to check if any records were
> returned and if so, go ahead with the update just like the code above
> does. Any ideas on how to do this?
>
> Thanks
>

Try this (I'm assuming you'll pass the parameters to a proc):

UPDATE meter_asset
SET meter_sort = @newsort,
update_flag = 'Y'
WHERE asset_id = @asset_id
AND EXISTS
(SELECT *
FROM asset
WHERE asset_id = @asset_id);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

aljamala@gmail.com

2006-10-24, 6:37 pm

> Try this (I'm assuming you'll pass the parameters to a proc):
>
> UPDATE meter_asset
> SET meter_sort = @newsort,
> update_flag = 'Y'
> WHERE asset_id = @asset_id
> AND EXISTS
> (SELECT *
> FROM asset
> WHERE asset_id = @asset_id);
>
I'm not so sure that would work, looking at it a little more closely I
noticed that the update is using values from the select..for example...

sAssetCD = objRS.Fields("asset_id").value

or updating fields found in the select...

objRS.Fields("meter_sort") = Trim(Request("newsort" & n))

aljamala@gmail.com

2006-10-24, 6:37 pm

> I'm not so sure that would work, looking at it a little more closely I
> noticed that the update is using values from the select..for example...
>
> sAssetCD = objRS.Fields("asset_id").value
>
> or updating fields found in the select...
>
> objRS.Fields("meter_sort") = Trim(Request("newsort" & n))

Is it even possible to do this in one stored procedure? I'm starting
to think its not

aljamala@gmail.com

2006-10-24, 6:37 pm

Any ideas if this would work properly? maybe its similar logic to the
where exists?....im gonna give it a try when i come back into work
tomorrow...but any ideas/suggestions would be appreciated...

CREATE PROCEDURE dbo.p_sel_asset
@asset_code varchar(20),
@newsort varchar(10)
AS

DECLARE @rowcount int

SELECT ast.asset_id, mast.meter_read_due_dt, mast.meter_sort,
mast.update_flag
FROM asset ast, meter_asset mast
WHERE (ast.asset_id = @asset_code)
AND (ast.asset_id = mast.asset_id)

SET @rowcount = @@rowcount

If @rowcount <> 0
BEGIN
UPDATE meter_asset
SET meter_sort = @newsort,
update_flag = 'Y'
WHERE (ast.asset_id = @asset_code)
AND (ast.asset_id = mast.asset_id)
END
GO

David Portas

2006-10-24, 6:37 pm

aljamala@gmail.com wrote:
> Any ideas if this would work properly? maybe its similar logic to the
> where exists?....im gonna give it a try when i come back into work
> tomorrow...but any ideas/suggestions would be appreciated...
>

What was wrong with the EXISTS version? Your UPDATE is not legal. If
you still have a problem then please post DDL and sample data so that
we can test it out.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

aljamala@gmail.com

2006-10-24, 6:37 pm

David Portas wrote:
> aljamala@gmail.com wrote:
>
> What was wrong with the EXISTS version? Your UPDATE is not legal. If
> you still have a problem then please post DDL and sample data so that
> we can test it out.
>

Nothing wrong with it, but it would not return any records for me where
I need two values for...

sAssetCD = objRS.Fields("asset_id")
dtMeterDate = objRS.Fields("meter_read_due_dt")

Sponsored Links