CONDITIONAL UPDATE 1

[LonnieJohnson]

How do I write an update query that would update certain fields based on the value of other fields? For example I want to do something like this.

UPDATE CUSTOMERS (DIST_CODE, PACKING_NUM)

VALUES

'HERE IS WHERE I AM LOST

IF COUNTY = 'WY' THEN DIST_CODE = A1 AND PACKING_NUM = 8
IF COUNTY = 'SG' THEN DIST_CODE = A2 AND PACKING_NUM = 9
IF COUNTY = 'DG' THEN DIST_CODE = A3 AND PACKING_NUM = 10

I KNOW WHAT I WANT TO DO, JUST CAN GET THE SYNTAX TO AGREE WITH ME

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[ClaireHCM]

This is one way, but personally I dont like it, because it scans the whole table.

update Customers Set Dist_Code =
(Select Case County when 'WY' Then 'A1' when 'SG' then 'A2' when 'DG' then 'A3' else Dist_Code end)
,PACKING_NUM =
(Select Case County when 'WY' Then 8 when 'SG' then 9 when 'DG' then 10 else PACKING_NUM end)

I am looking for alternative

 

[john76]

Does it have to all be in one update query?

BEGIN TRANSACTION Update_Records

UPDATE CUSTOMERS
  SET DIST_CODE = 'A1',
      PACKING_NUM = 8
  WHERE COUNTY = 'WY'

UPDATE CUSTOMERS
  SET DIST_CODE = 'A2',
      PACKING_NUM = 9
  WHERE COUNTY = 'SG'

UPDATE CUSTOMERS
  SET DIST_CODE = 'A3',
      PACKING_NUM = 10
  WHERE COUNTY = 'DG'

COMMIT TRANSACTION Update_Records

If you have an index on COUNTY, these queries ought to fly.

John

 

[donutman]

No alternative needed. It should be very fast as is. That's not to say that it couldn't be made faster by creating an index on County and possibly creating 3 seperate updates (if the data warrents), but c'mon... You can improve it slightly by eliminating the else clauses.
-Karl

 

[LonnieJohnson]

john,

I'm liking your method. I had some other stuff going on with a CASE STATEMENT and it was getting complicated. The example I gave you guys was a scaled down version of the real deal. I actually had a little more complex where statement. This is what I did and it whips right through.

BEGIN TRANSACTION Update_Records

	UPDATE MASTERFILE
	  SET AONBR = 27,
	      AREAOFFICE = 'LAWRENCE'
	  WHERE COUNTY = 'DG' 
	AND 
	(AONBR <> 27 OR AREAOFFICE <> 'LAWRENCE' OR AONBR IS NULL OR AREAOFFICE IS NULL)
	
	UPDATE MASTERFILE
	  SET AONBR = 26,
	      AREAOFFICE = 'KANSAS CITY'
	  WHERE COUNTY = 'WY' 
	AND 
	(AONBR <> 26 OR AREAOFFICE <> 'KANSAS CITY' OR AONBR IS NULL OR AREAOFFICE IS NULL)

COMMIT TRANSACTION Update_Records

I needed this because I am going to move a lot of stuff that gets updated via an access program each night to the SQL Server.

Thanks Again




ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[albertp]

UPDATE CUSTOMERS (DIST_CODE, PACKING_NUM)

VALUES

'HERE IS WHERE I AM LOST

IF COUNTY = 'WY' THEN DIST_CODE = A1 AND PACKING_NUM = 8
IF COUNTY = 'SG' THEN DIST_CODE = A2 AND PACKING_NUM = 9
IF COUNTY = 'DG' THEN DIST_CODE = A3 AND PACKING_NUM = 10

Do this:
Update Customers set Dist_Code = A1, Paking_Num =8
where County = WY
Update Customers set Dist_Code = A2, Paking_Num =9
where County = SG
Update Customers set Dist_Code = A3, Paking_Num =10
where County = DG