combine two update statements in query

[Access Rookie]

Good morning all,

I have a table that holds subscription information. In need to update 8
package descriptions along with a comment section. I'm open to suggestions
on the most efficient way to accomplish this task.

I was trying to get this task accomplished by creating an update query, but
I don’t know how to combine the update sql statements below.

Your assistance/suggestions will be greatly appreciated.

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = "COMP UPGRADE"
WHERE (((Tbl_Billing_Cons.Package)="LDCCSFDT"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = " EXTENSION "
WHERE (((Tbl_Billing_Cons.Package)="LDEXDPCV"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = "UPGRADE"
WHERE (((Tbl_Billing_Cons.Package)="LDSPSFDT"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = "UPGRADE SPECIAL"
WHERE (((Tbl_Billing_Cons.Package)="LDUPDRCT"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDLUXURY",
Tbl_Billing_Cons.OnStar_Notes = "COMP"
WHERE (((Tbl_Billing_Cons.Package)="LDCCLXRY"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDLUXURY",
Tbl_Billing_Cons.OnStar_Notes = " GOOD WILL "
WHERE (((Tbl_Billing_Cons.Package)="LDGWLXRY"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDLUXURY",
Tbl_Billing_Cons.OnStar_Notes = "UPGRADE"
WHERE (((Tbl_Billing_Cons.Package)="LDUPLXRY"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDSAFETY",
Tbl_Billing_Cons.OnStar_Notes = "SPECIAL"
WHERE (((Tbl_Billing_Cons.Package)="LDSPSFTY"))

 

[John Spencer MVP]

It may be faster to run the 8 separate queries then to try to combine them all
into one.

An alternative would be to build a table - tblConvert - with three columns
PackageOld
PackageNew
NewNote

That would contain records like:
LDCCSFDT : LDDIRECT : COMP UPGRADE
LDEXDPCV : LDDIRECT : EXTENSION
LDSPSFDT : LDDIRECT : UPGRADE
....
LDUPLXRY : LDLUXURY : UPGRADE
LDSPSFTY : LDSAFETY : SPECIAL

Now with that table you could do a simple join to tbl_billing_cons on
Tbl_Billing_Cons.Package and tblConvert.PackageOld to show the alternative values.

If you insist on doing an update, you could use the tblConvert to do so.

UPDATE Tbl_Billing_Cons INNER JOIN tblConvert
ON Tbl_Billing_Cons.Package = tblConvert.PackageOld
SET Tbl_Billing_Cons.Package = [tblConvert].[PackageNew]
, Tbl_Billing_Cons.OnStar_Notes = [tblConvert].[NewNote]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Access Rookie]

Thanks for the input. I'll try both option and use the one that is the
fastest.

It may be faster to run the 8 separate queries then to try to combine them all
into one.

An alternative would be to build a table - tblConvert - with three columns
PackageOld
PackageNew
NewNote

That would contain records like:
LDCCSFDT : LDDIRECT : COMP UPGRADE
LDEXDPCV : LDDIRECT : EXTENSION
LDSPSFDT : LDDIRECT : UPGRADE
....
LDUPLXRY : LDLUXURY : UPGRADE
LDSPSFTY : LDSAFETY : SPECIAL

Now with that table you could do a simple join to tbl_billing_cons on
Tbl_Billing_Cons.Package and tblConvert.PackageOld to show the alternative values.

If you insist on doing an update, you could use the tblConvert to do so.

UPDATE Tbl_Billing_Cons INNER JOIN tblConvert
ON Tbl_Billing_Cons.Package = tblConvert.PackageOld
SET Tbl_Billing_Cons.Package = [tblConvert].[PackageNew]
, Tbl_Billing_Cons.OnStar_Notes = [tblConvert].[NewNote]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Good morning all,

I have a table that holds subscription information. In need to update 8
package descriptions along with a comment section. I'm open to suggestions
on the most efficient way to accomplish this task.

I was trying to get this task accomplished by creating an update query, but
I don’t know how to combine the update sql statements below.

Your assistance/suggestions will be greatly appreciated.

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = "COMP UPGRADE"
WHERE (((Tbl_Billing_Cons.Package)="LDCCSFDT"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = " EXTENSION "
WHERE (((Tbl_Billing_Cons.Package)="LDEXDPCV"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = "UPGRADE"
WHERE (((Tbl_Billing_Cons.Package)="LDSPSFDT"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDDIRECT",
Tbl_Billing_Cons.OnStar_Notes = "UPGRADE SPECIAL"
WHERE (((Tbl_Billing_Cons.Package)="LDUPDRCT"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDLUXURY",
Tbl_Billing_Cons.OnStar_Notes = "COMP"
WHERE (((Tbl_Billing_Cons.Package)="LDCCLXRY"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDLUXURY",
Tbl_Billing_Cons.OnStar_Notes = " GOOD WILL "
WHERE (((Tbl_Billing_Cons.Package)="LDGWLXRY"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDLUXURY",
Tbl_Billing_Cons.OnStar_Notes = "UPGRADE"
WHERE (((Tbl_Billing_Cons.Package)="LDUPLXRY"))

UPDATE Tbl_Billing_Cons SET Tbl_Billing_Cons.Package = "LDSAFETY",
Tbl_Billing_Cons.OnStar_Notes = "SPECIAL"
WHERE (((Tbl_Billing_Cons.Package)="LDSPSFTY"))