Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Problem with SqlUpdate
Problem with SqlUpdate [message #45715] Tue, 29 December 2015 07:51 Go to next message
nilrum is currently offline  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 Go to previous messageGo to next message
dolik.rce is currently offline  dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic
Ultimate Contributor

Hi nilrum,

Welcome to the forum Cool

nilrum wrote on Tue, 29 December 2015 07:51
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?

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 #45725 is a reply to message #45723] Tue, 29 December 2015 17:48 Go to previous messageGo to next message
nilrum is currently offline  nilrum
Messages: 3
Registered: December 2015
Junior Member
Quote:

Welcome to the forum


Thanks Honza

Quote:

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'
Re: Problem with SqlUpdate [message #45728 is a reply to message #45725] Tue, 29 December 2015 20:05 Go to previous messageGo to next message
dolik.rce is currently offline  dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic
Ultimate Contributor

nilrum wrote on Tue, 29 December 2015 17:48
Quote:
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 Embarassed

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 Smile

Honza
Re: Problem with SqlUpdate [message #45729 is a reply to message #45728] Wed, 30 December 2015 05:58 Go to previous messageGo to next message
nilrum is currently offline  nilrum
Messages: 3
Registered: December 2015
Junior Member
Thanks Honza

How could I not think about it.. Embarassed
Re: Problem with SqlUpdate [message #45730 is a reply to message #45729] Wed, 30 December 2015 08:29 Go to previous message
dolik.rce is currently offline  dolik.rce
Messages: 1789
Registered: August 2008
Location: Czech Republic
Ultimate Contributor

nilrum wrote on Wed, 30 December 2015 05:58
Thanks Honza

How could I not think about it.. Embarassed

No need to be ashamed Wink 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 Very Happy

Honza
Previous Topic: Commas into SQL
Next Topic: MySql, boolean values and SqlArray
Goto Forum:
  


Current Time: Thu Mar 28 18:08:49 CET 2024

Total time taken to generate the page: 0.01436 seconds