Update Select with IS NOT Null

[franklin1232]

This is a simple update query, but it is causing me problems. The inner SELECT returns only rows with matching PLUNum through the INNER JOIN. However when K_Menu and C_Menu are joined there are addtional records returned and the PLUNum values for those addtional records is NULL. QSRCat and QSRDept that were already set in the C_Menu and shouldn't be changed by the query are set to NULL.

I tried simple PLUNum IS NOT NULL statement but I get correlation table errors. I tried to create alias but didn't work.

UPDATE C_Menu
SET QSRDept =
(SELECT PLUTable.QSRDept
FROM K_Menu INNER JOIN
PLUTable ON K_Menu.PLU = PLUTable.PLUNum
WHERE C_Menu.MenuID = K_Menu.MenuID)


UPDATE C_Menu
SET QSRCat =
(SELECT PLUTable.QSRCat
FROM K_Menu INNER JOIN
PLUTable ON K_Menu.PLU = PLUTable.PLUNum
WHERE C_Menu.MenuID = K_Menu.MenuID)

 

[gmmastros]

You should be able to combine both updates so that 1 query is used, instead of 2. This will speed up the operation.

By using coalesce, if the value in the PLUTable is NULL, then the value from the C_Menu table will be used instead. This effectively allows you to only update the value if there is something in PLUTable.

UPDATE    C_Menu
SET       QSRDept = Coalesce(PLUTable.QSRDept, C_Menu.QSRDept),
          QSRCat = Coalesce(PLUTable.QSRCat, C_Menu.QSRCat)
FROM      K_Menu 
          INNER JOIN PLUTable 
            ON K_Menu.PLU = PLUTable.PLUNum
WHERE     C_Menu.MenuID = K_Menu.MenuID

Make sense?

As always, with update statements, it's important that you make a backup of your database first.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry... there is an error with the above query. This one should work.

UPDATE    C_Menu
SET       QSRDept = Coalesce(PLUTable.QSRDept, C_Menu.QSRDept),
          QSRCat = Coalesce(PLUTable.QSRCat, C_Menu.QSRCat)
FROM      C_Menu
          Inner Join K_Menu 
            ON C_Menu.MenuID = K_Menu.MenuID
          INNER JOIN PLUTable 
            ON K_Menu.PLU = PLUTable.PLUNum

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[franklin1232]

Thanks it worked great. Never used Coalesce before. It even simplied the query. Thanks again.