CODEWITHSUNDEEP
sqlstored-procedures
Memor-X
Memor-X

Reputation: 2970

How can i create an SQL IF statement to check if fields need updating

I've created an update procedure that will change the values of a record in a table if the matching record in another table changed, however, how i've got it set up is that even though there is like 12 fields that can be updated, the procedure will update every one of them even if only 1 field has been changed

this is ofcause inefficient, it takes about 50 seconds for just 1 record, not good if there is like a few hundred, i thought up the psudocode for this easy enough

if olddata != newdata

    UPDATE!!!

endif

thing is that i have little to no experience with if statements in SQL, if i could work with PHP then it would be easy by just running a SELECT with a WHERE clause much like the if statement above then checking the number of rows it returned (if 0 then there is no change) but the procedure has to be just SQL, the best i could come up with is this

IF(SELECT o.field FROM originaltbale AS o INNER JOIN updatetable AS u ON o.primarykey = u.primarykey WHERE o.field <> u.field) != 0

     UPDATE!!!

ENDIF

but i doubt that'll work cause i'm sure SELECT won't return the number of rows, how can i create an if statement that'll work like this

Upvotes: 0

Views: 1187

Answers (2)

eaolson
eaolson

Reputation: 15090

I'm not sure I entirely understand what you're updating, but you're much better off doing this in a single statement and incorporating your IF into your WHERE clause.

UPDATE updatetable u
   SET ... something ...
 WHERE EXISTS ( SELECT 1 FROM originaltable o WHERE o.primarykey = u.primarykey AND ... )

But if you're storing the same field in two tables, are you sure this is the best database design for your tables?

Upvotes: 3

Vikdor
Vikdor

Reputation: 24134

If you want to know whether a row exists satisfying your criteria, then the EXISTS clause can be used.

-- If the original table doesn't have a record with the state same as that in updatetable
IF NOT EXISTS (SELECT o.field FROM originaltbale AS o INNER JOIN updatetable AS u ON o.primarykey = u.primarykey WHERE o.field <> u.field)
BEGIN

    UPDATE !!!

END

Hope this helps.

Upvotes: 2

Related Questions