|
Hello,
I want to perform a query like this:
UPDATE Payment SET Week = (SELECT Week FROM Receipt WHERE Payment = Payment.ID) Basically, the idea is that I have a table of payments, and a table of receipts, each receipt marked with a week number.
Now, I want to get the week number of the receipt for each payment, and put it in the Payments table.
To make things harder, there are two different receipts tables, and only one of the contains a receipt for a specific payment.
In pseudocode, it would be something like this:
foreach(paymentRow in Payments table)
{
WeekNum = SELECT Week FROM Receipts where ID_payment = paymentRow.ID_payment
IF WeekNum = NULL
{
WeekNum = SELECT Week FROM OtherReceipts WHERE ID_payment = paymentRow.ID_payment
}
IF WeekNum != NULL
UPDATE paymentRow SET Week = WeekNum
}It a once-in-a-lifetime update, so it doesn't have to be the best, performance-speaking.
So, any ideas?
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Ok, here's what might work:
UPDATE Payment
SET Week = WeekNum
FROM Payment
INNER JOIN Receipts ON Payment.ID_payment = Receipts.ID_payment
WHERE Receipts.WeekNum != NULL
UPDATE Payment
SET Week = WeekNum
FROM Payment
INNER JOIN OtherReceipts ON Payment.ID_payment = OtherReceipts .ID_payment
WHERE OtherReceipts.WeekNum != NULL
Basically each query tries to update Payment only when the WeekNum is not null.
Hope that helps,
Edbert
|
|
|
|
|
Wow!! Updates with joins? I was already reading about evil cursors 
Let me try this first!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
LuisR wrote:
Updates with joins? I was already reading about evil cursors
I can tell you that updates with joins are much more efficient than using cursors or looping via code.
You should learn how to update using joins as this will be your best friend for most of the time.
Edbert
|
|
|
|
|
Cool!! 
I tweaked it a bit, and it worked!
I didn't know updates with joins existed. But now I'll have a new trick!! I know cursors are not efficient, but this is a once in a lifetime update.
So, basically it's UPDATE table_to_update SET column = new_value FROM table_to_update like in a SELECT? This query should UPDATE all rows in the table. And if I add an INNER JOIN there, then the UPDATE will only work on the rows that would result from the same SELECT FROM statement?
Thanks!!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Yep.
The only limitation you need to remember is that UPDATE can only update 1 (one) table at a time (I'll be glad if somebody proves me wrong).
Therefore:
UPDATE <table1><br />
SET <specify table1 columns to update from values in all other tables specified below><br />
FROM <tables with JOIN and WHERE conditions>
Just for your information, do you know that you can also do
INSERT INTO (<columns>)<br />
(<br />
SELECT <columns><br />
FROM <tables><br />
)
|
|
|
|
|
Great!!
Edbert P. wrote:
Just for your information, do you know that you can also do
INSERT INTO ()
(
SELECT
FROM <tables>
)
Yes. I didn't but sometime ago someone taught me about it here (was it you maybe? or Colin?) and a few weeks ago I answered a question here with that INSERT INTO... SELECT. He didn't believe me until I said, "well, go and try it" and he was amazed!
SQL sometimes is so cool!
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Does anyone maybe know how I can open a sql server 2005 database in sql server 2000? I can't use "view all rows" in 2005, and need to use 2000 from now on.
Thanks
|
|
|
|
|
"Open Table" I believe is the equivalent menu option in SQL Server 2005
|
|
|
|
|
Hello!
I don't know how to connect to SqlServer when this server is not in the localmachine.
I'm using VB.Net and the server can only be joined on the internet.
My connect string looks this :
Cnx.ConnectionString = "Integrated Security=SSPI;Initial Catalog=name_of_my_base; Data Source=name_of_my_machine"
The computer where the server is running can be reached by a domaine name
Thank you for your help (and sorry for my bad english)
SeLoRBIS
|
|
|
|
|
SeLoRBIS wrote:
Cnx.ConnectionString = "Integrated Security=SSPI;Initial Catalog=name_of_my_base; Data Source=name_of_my_machine
Why is the data source the name of your machine?? It should be pointing to the server.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
I've repare my mistake , i've put my ip adresse = the domain name but any sql server was found .
I've cpyin the connection string proposed by the site : connection string.
but any server was found .
SeLoRBIS
|
|
|
|
|
Have you created the DSN name in your local machine pointing to the server that matches with the connection string?
Depending on the bindings that you are using you'll need to create it based on ODBC, ADO, ...
SeLoRBIS wrote:
I'm using VB.Net and the server can only be joined on the internet.
I've never tried to get data directly from Internet with that method  but it should work. Please, let me know if it works well using a DSN pointing to an Internet data server.
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
I use a dns name (domaine name) for resolve my ip which change with the deconnection .
This name never change .
I've try this connection with the base mySql and the connection run .
I think i go to use this base but i go to search the error with SQl Server!
But which solution do you use for get date directly from internet ?
SeLoRBIS
|
|
|
|
|
SeLoRBIS wrote:
But which solution do you use for get date directly from internet ?
I've always used a server (IIS[^]) that must have a DSN created pointing to the database. The connection string must specify the provider as "MS Remote":
set conn1 = new Connection
conn1.Open "Provider=MS Remote;" _
& "Remote Provider=MSDataShape;" _
& "Remote Server=http://www.domain_you_want.com;" _
& "Data Source=DNS_YOu_Specified;" _
& "User Id=User; Password=pwd;"
Marc Soleda
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
Hi,
my work hierarchy is as follows.
sqlStr = "select * from voucherTransaction where CompanyCode='" & compCode & _
"' and vtCode='" & mcmbVoucherType.SelectedItem.col1 & "' and
voucherNo=" & txtVoucherNo.Text
parameter.Value = sql // sqlCommand parameter object
davd.SelectCommand = cmd // cmd is sqlCommand and davd is sqlDataAdapter object
davd.Fill(dsvd, "voucherDetail") // dsvd is DataSet
davd.Dispose()
//after populating dataSet i need to delete a specific row . and the deletion
code is
dsvd.Tables("voucherDetail").Rows.RemoveAt(lastRow)//lastrow is secific row no
dsvd.Tables("voucherDetail").AcceptChanges()
//now i need to add new row as
dsvd.Tables("voucherDetail").Rows.Add(drow) // drow is DataRow
//now updating the DataSet as follows
sqlStr = "select * from voucherTransaction where CompanyCode='" & compCode & _
"' and vtCode='" & mcmbVoucherType.SelectedItem.col1 & "' and
voucherNo=" & txtVoucherNo.Text
parameter.Value = sqlStr
davd.SelectCommand = cmd
Dim cbvd As New SqlCommandBuilder(davd)
davd.Update(dsvd.Tables("voucherDetail"))
davd.Dispose()
Now the error is " can't insert the duplicate key value"
I have displayed the DataSet table (voucherDetail),there isn't any duplicate value but its showing error for that specific row because its not deleting from the database.
Please help me immediately if u have some suggestions
|
|
|
|
|
I'd like to know your opinions, experience and feelings about these DBs:
1. MSDE
2. PostgreSql
3. MySql
Thanks,
Marc Soleda.
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
1. MSDE:
I did not use MSDE (but used MS SQL Server 2000)
It's free with many features (stored procedures/UDF/ocnstraints/triggers/some types of replication) you can consider it a limited SQL Server version regarding performance.
2. PostgreSql:
I did not use it either..But it's free,open source and reliable and semi full featured.
3. MySql:
I used it and I like it..It's free,open source,very fast,and widly used on the internet.But it's features are less complete compared to the previous DBMSs.
the decision of using one against the others depends on the project you make and your previous knowledge.
|
|
|
|
|
Hi all,
Facing small problem.
I have one field of type SmallDateTime in SQL Server table and contains both date and time as a data. I want to get all records of specific date irrespective of time and i given following query like
select firstname,paiddate from tbluser where paiddate = '24-mar-2005'
is not showing any record because time part is also there.
so what is the standard method to get all records of a particular date ignoring time part (not by using string functions like substring or left etc)
Thanks for reply.
|
|
|
|
|
Hi, this is how I do it:
SELECT * FROM FOO
WHERE BAR >= '2005-02-20' AND BAR < DATEADD(D, 1, '2005-02-20')
Hope this helps.
|
|
|
|
|
select firstname,paiddate from tbluser where datediff(day,paiddate,'24-mar-2005')=0
|
|
|
|
|
Dear Friends,
I have and XML File and am binding that xml file to a datagrid using dataview with a check box to select the row to update or delete.
In the event of check box I used to find row from dataview using sort property and find() method.
But I got the row number more than one than the actual one.
e.g. when I got rowno = 1 as a result of find method, in dataview that row is actually at postition 0. so every time next row of the actual row is deleted or modified.
So if anyone have the solution of this then plz send it to me quickly .
Thanking u in advance
Chetan Ranpariya
|
|
|
|
|
Asp
connect to ado/ms access file string is like that
conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("testing.mdb") & ";UID=Admin;PWD=password"
How to set the UID and PWD for the "testing.mdb"?
I have tried to set them up with MS Access but it's fail.
Thanks
|
|
|
|
|
ting668 wrote:
I have tried to set them up with MS Access but it's fail.
How have you tried? Via "tools > security > set password"?
Marc Soleda
... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.
|
|
|
|
|
Yes, but it only set the database password but what is the login?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
