Home » U++ Library support » U++ SQL » Problem with SqlUpdate
| Problem with SqlUpdate [message #45715] |
Tue, 29 December 2015 07:51  |
nilrum
Messages: 3
Registered: December 2015
|
Junior Member |
|
|
Hi all!
How can I run the following query using the Sql Update:
update LINK set ID_OTHER = (select ID_TWO from TWO where NAME_TWO = 'Name')
where ID_LINK = 1
I tried the request, but is it wrong
SqlUpdate(LINK)(SqlSet(ID_OTHER), SqlSet(Select(ID_TWO).From(TWO).Where(NAME_TWO == "Name"))).Where(ID_LINK == 1);
==>
update LINK set (ID_OTHER) = (select ID_TWO from TWO where NAME_TWO = 'Name')
where ID_LINK = 1
How to remove the brackets around ID_OTHER?
|
|
|
|
| Re: Problem with SqlUpdate [message #45723 is a reply to message #45715] |
Tue, 29 December 2015 16:20  |
|
|
Hi nilrum,
Welcome to the forum 
nilrum wrote on Tue, 29 December 2015 07:51I tried the request, but is it wrong
SqlUpdate(LINK)(SqlSet(ID_OTHER), SqlSet(Select(ID_TWO).From(TWO).Where(NAME_TWO == "Name"))).Where(ID_LINK == 1);
==>
update LINK set (ID_OTHER) = (select ID_TWO from TWO where NAME_TWO = 'Name')
where ID_LINK = 1
How to remove the brackets around ID_OTHER?
The problematic brackets come from SqlSet(). You actually want just a single value, not a set of values:SqlUpdate(LINK)(ID_OTHER, SqlSet(Select(ID_TWO).From(TWO).Where(NAME_TWO == "Name"))).Where(ID_LINK == 1);
This should work as you intended.
Best regards,
Honza
|
|
|
|
|
|
| Re: Problem with SqlUpdate [message #45728 is a reply to message #45725] |
Tue, 29 December 2015 20:05 |
|
|
nilrum wrote on Tue, 29 December 2015 17:48Quote:The problematic brackets come from SqlSet(). You actually want just a single value, not a set of values:
SqlUpdate(LINK)(ID_OTHER, SqlSet(Select(ID_TWO).From(TWO).Where(NAME_TWO == "Name"))).Where(ID_LINK == 1);
This should work as you intended.
So I tried to write, but I receive an error
error C2664: 'Upp::SqlUpdate &Upp::SqlUpdate::operator ()(const Upp::SqlId &,Upp::SqlVal)' : cannot convert argument 1 from 'Upp::SqlId' to 'const Upp::SqlSet &'
Reason: cannot convert from 'Upp::SqlId' to 'const Upp::SqlSet'
I'm sorry that I mislead you... I haven't actually check if the code compiles 
I had to check the code of SqlSelect and I finally figured out how this should be done properly:SqlUpdate(LINK)(ID_OTHER, Select(ID_TWO).From(TWO).Where(NAME_TWO == "Name").AsValue()).Where(ID_LINK == 1);
Notice the AsValue() method of SqlSelect. It turns the subquery into SqlVal, which can be then assigned to ID_OTHER column. There is also similar method AsTable(), which does similar job in case of subqueries in FROM clause.
Hope this will finally work 
Honza
|
|
|
|
|
|
| Re: Problem with SqlUpdate [message #45730 is a reply to message #45729] |
Wed, 30 December 2015 08:29 |
|
|
nilrum wrote on Wed, 30 December 2015 05:58Thanks Honza
How could I not think about it..
No need to be ashamed  There is very little documentation on the SQL stuff. The tutorials only cover basic stuff, if you get into subqueries and other complex tasks, you sometimes just have to go to look at the source.
Also, I got it wrong fist time too 
Honza
|
|
|
|
Goto Forum:
Current Time: Mon Apr 28 23:10:17 CEST 2025
Total time taken to generate the page: 0.03408 seconds
|
Members
Pages
Help
Register
Login
Home
