a SQL quation

[Serdar C.]

hello everyone,

i am working on a program that keeps a company's stock's .. everytthing
works fine but i am trying to do something like this:

first there are 2 tables in the database:

C1-C4: Column1 through column4

Table1 Table2
C1 C2 C3 C4
+---------+--------+ +---------+--------+
| Value1 | Value2 | | Value3 | Value4 |
+---------+--------+ +---------+--------+

i want to do something like updating the "Value2" like,
"Value2 = Value2-Value3"

i tried a sql command like this which i knew it wont work but i tried anyway

" update Table1 set C2 = (C2 - (select C4 from table2 where C3 = 'value3'))
where C1 = 'Value1' "

i hope u got the point..

thanx for spending your time on reading and thanx more for you help
efforts..

 

[Nick Malik [Microsoft]]

That entire concept doesn't make sense. SQL is a set-oriented language. A
field can only really be in one position in an expression, since sets define
a consistent stable state, not a transition at a point in time.

In your description, C2 is both an operand and a result. Therefore, there
is no way to do what you want.

On the other hand, if you have a table that contains terms, and you have a
result field in another table, and you can make a sensible join between
them, then you can use something like the SUM function to perform basic math
and store your result.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.

 

[Jon Skeet [C# MVP]]

Serdar C. <"Serdar C." <serdarc@(dont write this part)

i am working on a program that keeps a company's stock's .. everytthing
works fine but i am trying to do something like this:

first there are 2 tables in the database:

C1-C4: Column1 through column4

Table1 Table2
C1 C2 C3 C4
+---------+--------+ +---------+--------+
| Value1 | Value2 | | Value3 | Value4 |
+---------+--------+ +---------+--------+

i want to do something like updating the "Value2" like,
"Value2 = Value2-Value3"

i tried a sql command like this which i knew it wont work but i tried anyway

" update Table1 set C2 = (C2 - (select C4 from table2 where C3 = 'value3'))
where C1 = 'Value1' "

i hope u got the point..

thanx for spending your time on reading and thanx more for you help
efforts..

I suggest you ask in the SQL group instead of the C# group - this isn't
really a C# question.

 

[Jimbo]

hello everyone,

" update Table1 set C2 = (C2 - (select C4 from table2 where C3 = 'value3'))
where C1 = 'Value1' "

Not a chance within a single SQL statement if you are using MS SQL
Server or Oracle then put the individual SQL statements and logic into
stored procedures.

I am guessing that this one table is the users shopping basket and the
other is the main stock table. If this is the case then this approach
will be fine to execute for each item put into the basket.

Maybe take a look at the Microsoft Pet Shop example:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/petshop3x.asp

Cheers
Jimbo

 

[oj]

You can do that only if there is a way to reference table1 to table2.

e.g.
update table1
set c2=c2-(select c4 from table2 where c3=table1.c1)
where c1='abc'