|
Thanks Pete,
I will be more specific, I tried your suggestion but still doesn't work...I'm not an SQL expert  )
I will rite here the involved tables and their main fields:
Orders(OrderId)
OrderDetail(OrderId, ProductSubCode, Quantity)
Products(ProductSubCode, Quantity)
When an order is canceled I want to get all the items on it and re-insert them in the Product table (summarizing with the amount already in that table)
If I do a select of all the items in a given order as you told me I will get something like this:
"SELECT * INTO #TMP FROM ORDERDETAIL WHERE ORDERID=30"
OrderId ProductSubCode Quantity
30 BD0002 10
30 BD0004 4
Now I need to re-insert those quantities into the Products table.
I can't figure out how I ca do that without a cursor since I need to iterate through the records in the temporary table.
Of course I can still be missing alternatives
Thanks a lot.
|
|
|
|
|
OK - here's a sample way to update the product table:
UPDATE Products
SET p.Quantity = p.Quantity + t.Quantity
FROM Products p, #Tmp t
WHERE p.ProductSubCode = t.ProductSubCode This doesn't involve any cursors and is completely set based.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks a lot Pete!!!!
That worked.
As you stated there was not necessary a cursor here!!
|
|
|
|
|
No problems. I'm glad that I could help.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
how to update feilds from two different tables
and insert updated values into orignal tables.
|
|
|
|
|
probably by writing two insert statements.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
hi all
i have 2 table province and distric is one_many
in province and distric have column "code" datatype is interger
data on column code Province is length = 3 Ex("101 or 302 or 343")
in the data on column code distric is length = 5 Ex 10101" or "30234")
The data both column code in 2 table have there character fist is same:
EX Province is NY: 101 so all distrcit into NY have there character fist is 101 and add two character is: 10101 , 10102, 10103
how can select * from distric and get column name on the province
?????
|
|
|
|
|
Well, you've broken the first normal form by storing two pieces of information inside one column. You need to split out the province's code from district's code in the district table. You can also duplicate the province's code in the district.
You can then join on the common province codes in each table. Once that is done, you can then write a statement like this:
SELECT province.name, district.* FROM district
INNER JOIN province ON province.code = district.province_code
|
|
|
|
|
Is it possible to select from 3 tables, and insert into a new table without using a where clause and using the SQL cursor. If so, how would I do that? Any code example?
|
|
|
|
|
The examples that come with the help for SQL Server show how to use a cursor. You can use a cursor on any select, across any number of tables. As you step through it, you an insert into a new table from each row.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I have to statements inside the StoredProcedure. If in the first satement occured Error I want exit from the StoredProcedure. For this Scenario, How to write StoredProcedure
Mohan Balal
|
|
|
|
|
You can use try-catch block to catch the exceptions..
See THIS
PS. you are asking basic questions...try and search for them on google before asking. Take an initiative yourself and we will help you in it, but if you want us to take an initiative for you YOU MISS-READ THE IDEA OF CODE PROJECT .
Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.
|
|
|
|
|
Thanks for your Reply and kind suggestions
Mohan balal
|
|
|
|
|
select count(*) from Emp
Select count(empid) from Emp
what is the different between above two sql statements
Mohan Balal
|
|
|
|
|
Count(*)
returns the number of items in a group, including NULL values
Count(fieldName)
returns the number of items in a group, excluding NULL values
Hope it helps
Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.
|
|
|
|
|
Thanks a lots
Mohan balal
|
|
|
|
|
what is the difference between
SELECT group_name
FROM groups,users_groups
WHERE users_groups.user_id=x and groups.group_id=x
VS
SELECT group_name
FROM groups
INNER JOIN users_groups
ON users_groups.user_id = groups.group_id
|
|
|
|
|
In the first statement you leave it up to the sql query parser to decide how to join the tables. In the second case you are telling the query parser how you want the two tables joined. With only two tables it may not be a big deal other then the first example is slopy. Once you start to have several tables you are trying to join to your tables may not join together they way you want them to. I would sugest always use the second method. It make your code clear as far as what you are trying to do.
Hope that helps.
Ben
|
|
|
|
|
Hello all,
I'm looking for what you would consider the best book on SQL Server 2005 Stored Procedures.
Looking for suggestions, what would you recommend?
Thanks in advance,
Silvio
http://www.silvio.us
-- modified at 13:44 Sunday 15th April, 2007
|
|
|
|
|
Dear all,
I would like to insert data entered into datagridview(dgv1) into the two fields (with name "code" and "qty") of a database table "stock"
Below is the code. The problem is that the codes run smoothly without error but the data entered into the DGV1 is not inserted into the database:
1. Here are the code at form load. I populate the DGV (with name dgv1) with the existing data of the table 'stock':
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim tbl As New DataTable
tbl.Columns.Add("s_code", GetType(String))
tbl.Columns.Add("S_name", GetType(String))
Dim constr As String = "data source=DADDY-desktop;initial catalog=Fund_System;Integrated Security=True"
Dim sqlstr As String = "select * from stock"
dgv1.DataSource = bs
da = New SqlDataAdapter(sqlstr, constr)
Dim table As New DataTable()
da.Fill(table)
tbl = table
bs.DataSource = tbl
End Sub
2. Here are the codes initiated by a click of a button which was hit after the user has entered code into the datagridview dgv1:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlcon As String = "data source=DADDY-desktop;initial catalog=Fund_System;Integrated Security=True"
Dim con As New SqlConnection(sqlcon)
con.Open()
da.InsertCommand = New SqlCommand("insert into stock VALUES (@code, @qty)", con)
Dim pc As SqlParameterCollection
pc = da.InsertCommand.Parameters
pc.Add("@code", SqlDbType.Char, 10, "code")
pc.Add("@qty", SqlDbType.Char, 10, "qty")
Dim tbl As DataTable = New DataTable("cus_table")
da.Fill(tbl)
dgv1.DataSource = tbl
da.Update(tbl)
con.Close()
End Sub
bindingsource bs and DataAdapter da are declare outside of 1 and 2 above, under the Public Class Form1 heading:
Private bs As New BindingSource()
Private da As New SqlDataAdapter()
Can anybody point out what's wrong with the code?
Thank you very much.
|
|
|
|
|
Hellow to all ..
i am trying to Import Data into my sql server 2000 , the data is Access database when i creat new database, i go to tables and go into the wizard i follow the steps and when i finsh i don't see any tables ...
but if i import data to a database name Master , i can see the tables ..
anyidea ?
thxx for anyhelp ..
|
|
|
|
|
During the Wizard Import and Export process when prompted to identify a destination did you select New and then name it?
Learning is not a spectator sport. - D. Blocher
|
|
|
|
|
thxx my friend i was doing things fine , but i should refresh the list in order to see the new tables ..
thxx
|
|
|
|
|
hi,i'm new here,it's my first time to ask a question..
I'm trying to join 2 tables together using sql query as following:
insert into table1
select * from
table2
but if there's a record in table2 which disobey referential integrity constraints,the query would be terminated,and the rest were failed to insert.
I have also tried to use cursor to fetch record one by one,but it's too trouble to judge every column type.
I'm looking for some efficient solutions..
oh,I'm so sorry for my poor english!
Thanks a lot in advance,
ChongHui.Ren
-- modified at 8:07 Saturday 14th April, 2007
|
|
|
|
|
I am assuming that the problem you have is that some of the records in table2 already exist in table1. So your query would need to be:
insert into table1
select t2.* from
table2 t2
left join table1 t1 on t2.key = t1.key
where t1.key is null
I am assuming you have a column in both tables called key, most likely you call the column something else. The select statement will only give you the records in table2 that are not already in table1
Hope that helps.
Ben
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
