Issa S.
asked on
Where does the SQL and Design Columns Hide when converting a Select Query To an Update?
Hi All,,,
Q: Where does the SQL and Design Columns Hide when converting a Select Query To an Update?
I am using the Query Design for most of my work, so the SQL below is as generated by the system.
My SQL for a Select Query is :
My SQL for the Select Q is :
SELECT [Returned-Voucher-Recs].Rt rnVouchNo, [Returned-Voucher-Recs].co de, [Returned-Voucher-Recs].Qt yRegThisRt rn, [Returned-Voucher-Recs].Qt yTstrThisR trn, [Returned-Voucher-Recs].Qt yBonusThis Rtrn, (-1)*(Nz([Returned-Voucher -Recs]![Qt yRegThisRt rn],0)+Nz( [Returned- Voucher-Re cs]![QtyTs trThisRtrn ],0)+Nz([R eturned-Vo ucher-Recs ]![QtyBonu sThisRtrn] ,0)) AS GTotPerItem, GStocks.Available, Nz([GStocks]![Available],0 )+[GTotPer Item] AS AddedGT2Avail
FROM [Returned-Voucher-Recs] INNER JOIN GStocks ON [Returned-Voucher-Recs].co de = GStocks.code
WHERE ((([Returned-Voucher-Recs] .RtrnVouch No)=[ which Return Voucher You To Return ]));
After viewing its behavior and make sure it yields the required results, I convert it to an Update.
UPDATE [Returned-Voucher-Recs] INNER JOIN GStocks ON [Returned-Voucher-Recs].co de = GStocks.code SET GStocks.Available = [AddedGT2Avail]
WHERE ((([Returned-Voucher-Recs] .RtrnVouch No)=[ which Return Voucher You To Return ]));
As you can see in the second SQL for the Update one, I do not see the Expressions that I used when building it. So, if I want to revert it to Select for checking in the Design mode, I do not see except the column that is going to be updated and the column for the Criteria.
The Query still gives the same results but I have no idea about the Expressions that I made! What if I need to modify in the future ? So where do these hide?
Someone to help please.
Q: Where does the SQL and Design Columns Hide when converting a Select Query To an Update?
I am using the Query Design for most of my work, so the SQL below is as generated by the system.
My SQL for a Select Query is :
My SQL for the Select Q is :
SELECT [Returned-Voucher-Recs].Rt
FROM [Returned-Voucher-Recs] INNER JOIN GStocks ON [Returned-Voucher-Recs].co
WHERE ((([Returned-Voucher-Recs]
After viewing its behavior and make sure it yields the required results, I convert it to an Update.
UPDATE [Returned-Voucher-Recs] INNER JOIN GStocks ON [Returned-Voucher-Recs].co
WHERE ((([Returned-Voucher-Recs]
As you can see in the second SQL for the Update one, I do not see the Expressions that I used when building it. So, if I want to revert it to Select for checking in the Design mode, I do not see except the column that is going to be updated and the column for the Criteria.
The Query still gives the same results but I have no idea about the Expressions that I made! What if I need to modify in the future ? So where do these hide?
Someone to help please.
if you want to update 2 columns use like below- , You have to use the set command, that is missing.
UPDATE a
SET a.[columnename] = GStocks.[columnnmae] , a.[column2] = GStocks.[columnnmae2]
FROM [Returned-Voucher-Recs] a INNER JOIN GStocks ON a.code = GStocks.code SET GStocks.Available = [AddedGT2Avail]
WHERE (((a.RtrnVouchNo)=[ which Return Voucher You To Return ]));
Refer update command syntax here for more details -
https://msdn.microsoft.com/en-us/library/ms177523.aspx
https://www.techonthenet.com/sql/update.php
Hope it helps!
UPDATE a
SET a.[columnename] = GStocks.[columnnmae] , a.[column2] = GStocks.[columnnmae2]
FROM [Returned-Voucher-Recs] a INNER JOIN GStocks ON a.code = GStocks.code SET GStocks.Available = [AddedGT2Avail]
WHERE (((a.RtrnVouchNo)=[ which Return Voucher You To Return ]));
Refer update command syntax here for more details -
https://msdn.microsoft.com/en-us/library/ms177523.aspx
https://www.techonthenet.com/sql/update.php
Hope it helps!
Hi,
Please use this for your code. I have modified the code for you.
Hope it helps!
Please use this for your code. I have modified the code for you.
UPDATE b
SET b.Available = [AddedGT2Avail]
FROM
[Returned-Voucher-Recs] a INNER JOIN GStocks b ON a.code = b.code
WHERE a.RtrnVouchNo = [ which Return Voucher You To Return ]
Hope it helps!
First of all, I believe you are talking about Access.
It is an annoying feature of Access. When you convert to Update it keeps only the real columns that could be updated. The expressions are all gone. If you want to save your expressions then save the existing query before converting it to UPDATE. Then, you have two options:
It is an annoying feature of Access. When you convert to Update it keeps only the real columns that could be updated. The expressions are all gone. If you want to save your expressions then save the existing query before converting it to UPDATE. Then, you have two options:
- convert to Update, execute your Update query and close without saving
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Removed SQL Server topics and added MS Access topic to call the proper Experts.
You may keep a select version of the query.
Employees_bonus_select.
Employees_bonus_update.
Employees_bonus_select.
Employees_bonus_update.
Where does the SQL and Design Columns Hide when converting a Select Query To an Update?They are ignored, Access only keeps the columns that are updated, and they show in the set part of the update query.
ASKER
Dear Chaau,,,
Sorry, I meant ACCESS.
Is this for the Update Query only or can happen for other types like Make, Delete, Append, and/or Crosstab ?
The crazy thing is that you don't see the Expression in the Design layout nor in the SQL !!
Thanks
Sorry, I meant ACCESS.
Is this for the Update Query only or can happen for other types like Make, Delete, Append, and/or Crosstab ?
The crazy thing is that you don't see the Expression in the Design layout nor in the SQL !!
Thanks
I am pretty sure the expressions will be preserved for the Make, Append queries. As for the Delete and Crosstab - test it yourself.
ASKER
The big question now is that:
Is it save to run it as is since it gives the correct results as anticipated ?
Is it save to run it as is since it gives the correct results as anticipated ?
Sorry Issa, I did not understand your question
ASKER
I meant what do you suggest to me now ?
Shall I keep it as is and let it run because it is going to be a part of a Macro whereby other queries will depend on its updated results in the related table?
Of course, I already created a shadow for it in a Select Query in case I need to come back to it to revise the Expression or anything else ?
Thanks again.
Shall I keep it as is and let it run because it is going to be a part of a Macro whereby other queries will depend on its updated results in the related table?
Of course, I already created a shadow for it in a Select Query in case I need to come back to it to revise the Expression or anything else ?
Thanks again.
ASKER
BTW: Is it save to run it as is since it gives the correct results as anticipated ?
* safe not save, sorry
* safe not save, sorry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes Chaau,,,
That is correct. [AddedGT2Avail] is my expression that calculates. This is a column in my Design view as well.
Simply like a = a + c . i.e. I am increasing a with c.
C is "[GTotPerItem]" which is also a calculated column in the following Expression:
GTotPerItem: (-1)*(Nz([Returned-Voucher -Recs]![Qt yRegThisRt rn],0)+Nz( [Returned- Voucher-Re cs]![QtyTs trThisRtrn ],0)+Nz([R eturned-Vo ucher-Recs ]![QtyBonu sThisRtrn] ,0))
In terms of the result, it is perfect. But I can not see both " [GTotPerItem]" and "[AddedGT2Avail]" when I go back to the design view especially and immediately after saving the Query.
I hope this helps
That is correct. [AddedGT2Avail] is my expression that calculates. This is a column in my Design view as well.
Simply like a = a + c . i.e. I am increasing a with c.
C is "[GTotPerItem]" which is also a calculated column in the following Expression:
GTotPerItem: (-1)*(Nz([Returned-Voucher
In terms of the result, it is perfect. But I can not see both " [GTotPerItem]" and "[AddedGT2Avail]" when I go back to the design view especially and immediately after saving the Query.
I hope this helps
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear hnasr,,,
Thanks but can you tell me how to reflect this into my given SQL at the beginning of this thread because I am poor in writing SQL and this code was ACCESS-generated by the Query Design and not by me?
Thanks but can you tell me how to reflect this into my given SQL at the beginning of this thread because I am poor in writing SQL and this code was ACCESS-generated by the Query Design and not by me?
ASKER
Thanks for every one, I learned a lot from you.
Problem is solved
Problem is solved
Dear hnasr,,,Welcome!
Thanks but can you tell me how to reflect this into my given SQL at the beginning of this thread
This is your select statement: The changes may not work, but only for comparison.
SELECT [Returned-Voucher-Recs].RtrnVouchNo, [Returned-Voucher-Recs].code, [Returned-Voucher-Recs].QtyRegThisRtrn, [Returned-Voucher-Recs].QtyTstrThisRtrn, [Returned-Voucher-Recs].QtyBonusThisRtrn, (-1)*(Nz([Returned-Voucher-Recs]![QtyRegThisRtrn],0)+Nz([Returned-Voucher-Recs]![QtyTstrThisRtrn],0)+Nz([Returned-Voucher-Recs]![QtyBonusThisRtrn],0)) AS GTotPerItem, GStocks.Available, Nz([GStocks]![Available],0)+[GTotPerItem] AS AddedGT2Avail
FROM [Returned-Voucher-Recs] INNER JOIN GStocks ON [Returned-Voucher-Recs].code = GStocks.code
WHERE ((([Returned-Voucher-Recs].RtrnVouchNo)=[ which Return Voucher You To Return ]));
You select the fields and expressions needed to view about your already entered data. Notice the expression "already entered"
To change this to an update query:
Still you deal with already entered data, but wants to modify a few of the values in a record. Here you have to tell access which field/fields you intend to modify.
Update [Returned-Voucher-Recs] INNER JOIN GStocks ON [Returned-Voucher-Recs].code = GStocks.code
Set [Returned-Voucher-Recs].code = xxxxx
WHERE ((([Returned-Voucher-Recs].RtrnVouchNo)=[ which Return Voucher You To Return ]));
As you see here access only knows the table and the where clause, but has no idea about you intention to which field to update. When you change a select to an update, it displays.
Update [Returned-Voucher-Recs] INNER JOIN GStocks ON [Returned-Voucher-Recs].code = GStocks.code
SET
WHERE ((([Returned-Voucher-Recs].RtrnVouchNo)=[ which Return Voucher You To Return ]));
Please use like below-
Your update syntax in incorrect.
Open in new window
Hope it helps!