Update multiple fields using Inner Join and an if or where 2

[MsHart2u]

What is the best way to update multiple fields in a record only if the values are different?

Ive tried the following two scripts to only receive errors...
############# 1 ##################
UPDATE tbl_title
set
retail_us = tbl_scan_temp.retail_us
where (retail_us <> tbl_scan_temp.retail_us) and (tbl_scan_temp.retail_us is not null),
bisac_code = tbl_scan_temp.bisac_code
where (bisac_code <> tbl_scan_temp.bisac_code) and (tbl_scan_temp.bisac_code is not null),
length = tbl_scan_temp.length
where (t.length <> tbl_scan_temp.length) and (tbl_scan_temp.length is not null) ,
width = tbl_scan_temp.width
where (t.width <> tbl_scan_temp.width) and (tbl_scan_temp.width is not null)
FROM tbl_scan_temp
INNER JOIN tbl_title t ON t.isbn = tbl_scan_temp.isbn

############# 2 ##################
UPDATE tbl_title
if (retail_us <> tbl_scan_temp.retail_us) and (tbl_scan_temp.retail_us is not null) then
set retail_us = tbl_scan_temp.retail_us
if (bisac_code <> tbl_scan_temp.bisac_code) and (tbl_scan_temp.bisac_code is not null)then
set bisac_code = tbl_scan_temp.bisac_code
if (t.length <> tbl_scan_temp.length) and (tbl_scan_temp.length is not null) then
set length = tbl_scan_temp.length
if (t.width <> tbl_scan_temp.width) and (tbl_scan_temp.width is not null) then
set width = tbl_scan_temp.width
FROM tbl_scan_temp
INNER JOIN tbl_title t ON t.isbn = tbl_scan_temp.isbn


The temp table is always thousands of records, so I hope it is clear by my poor efforts that I only want to chnge the value if it is valuable and without cycling the tables more than once.

Thanks for your help once again.

[blue]Kathy[/blue]
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[philhege]

You can use only one WHERE clause in an UPDATE statement. Change your IF constructions to CASE statements and you may be able to get away with #2.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[hmckillop]

What you need is CASE try

UPDATE  t
    set 
     t.retail_us = 
        CASE  WHEN t.retail_us <> tmp.retail_us and tmp.retail_us is not null 
			THEN tmp.retail_us 
			ELSE t.retail_us 
		END,
     t.bisac_code = 
			CASE WHEN t.bisac_code <> tmp.bisac_code and tmp.bisac_code is not null 
				THEN tmp.bisac_code 
				ELSE t.bisac_code 
			END,
     t.length = 
        CASE WHEN t.length  <> tmp.length  and tmp.length is not null 
			THEN tmp.length 
			ELSE t.length
		END,
     t.width =
		
        CASE WHEN t.width <> tmp.width AND tmp.width is not null
			THEN tmp.width
			ELSE t.width
		END

FROM tbl_scan_temp tmp
INNER JOIN  tbl_title t ON t.isbn = tmp.isbn

"I'm living so far beyond my income that we may almost be said to be living apart

 

[MsHart2u]

Thanks alot hmckillop. Your Update-Case statement worked perfectly!

[blue]Kathy[/blue]
When you dig another out of their troubles, you find a place to bury your own.—Anonymous
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb