|
Hi,
Try this :
ColumnA is the primary key of table : tableName
UPDATE tableName SET columnB = 9 + t2.RowNum FROM tableName t1 INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY columnA) RowNum,columnA FROM tableName) t2 ON t1.columnA = t2.columnA
|
|
|
|
|
I'm having a temp table in which I'm inserting data. In temp table data is ok and I want to update another table with temp table data. I'm new to SQL My Query is:
update result_table set result_table.a = temp.a, result_table.b = temp.b from temp where temp.a= 1
I'm deleting the data in temp table and inserting data again in temp table and updating the result table.
How can I do this?
Please share your opinions.
Thanks!
|
|
|
|
|
You probaly want a JOIN in the FROM part -- JOIN the result_table to temp ON some key you haven't mentioned.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I got these very weird scenario i encounter.
it happens when i try execute the sample provided in mysql doc about prepared statement, i created the table with columns correspond with the sample. mysql prepared statement
then when i execute it(BTW i used the c# sample), it populate the rows with 1000 entries.
after that i try to insert one entry with out the prepared statement (at this moment the ignore prepare property is set to true), then the executeNonQuery return 1, so it means it inserted the so i checked the database but the value i inserted is not in there, so i try it many time but still no luck.
so i decide to write the insert query directly to workbench, but i got thesame result the data was not there.
so i tried to delete the last row, then the weird things happen. it delete the the last row that has an the id of 1000(btw the id was autoincrement) it also insert 1 new data with the id of 1001, so try to delete to rows but after the two rows was deleted it also insert two more rows with the id of 1002 and 1003.
then after that i tried to insert new data but is still keep telling me that 1 row is inserted but i cant see that on the table so, i tried to delete rows still bu t it still doing the same.
BTW as u can see on the documentation the second column is a number, then when i delete the data it also insert now row with the incremented value of it.
so i decide to truncate the table and insert few row and then delete some of it, at this point it was working as expected, then i decide to execute the prepared statement(ignore prepare set to false) then after that it do the same weird things.
i dont know what on this, could somebody explain to me.
i will appreciate for any enlightenment will come.
thank you very much
|
|
|
|
|
Can you show us the query you are using to check the new rows, you allegedly inserted? You may also add your insert command...
Also read here to understand what ExecuteNonQuery exactly returns - http://waldev.blogspot.co.il/2008/01/return-value-of-executenonquery-in.html[^]
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
actually I am using workbench and sqlyog for browsing the database.
executing insert or delete, ExecuteNonQuery return the expected number of rows to be affected on the console, but when i look on the database the affected rows is not there. even i will execute directly it to workbench or sqlyog i got the same behavior.
|
|
|
|
|
SELECT
[Sheet1$].Sheet1DetailId,
[Sheet1$].Sheet1Id,
[Sheet2$].Sheet2Id,
LTrim(RTrim([Sheet3$].Mission)) + '-' + CStr(Format('01-' + [Sheet3$].Sheet3Date,"mm/dd/yyyy")) AS Sheet3DateColumn,
[Sheet1$].one,
[Sheet1$].two,
[Sheet1$].three,
[Sheet1$].four,
[Sheet1$].five,
[Sheet1$].six,
[Sheet1$].seven,
[Sheet1$].eight,
[Sheet1$].nine,
[Sheet1$].ten,
LTRIM(RTRIM([Sheet2$].Name)) AS Sheet2Name
FROM (([Sheet1$])
INNER JOIN [Sheet2$] ON [Sheet1$].Id = [Sheet2$].Sheet2Id)
INNER JOIN [Sheet3$] ON [Sheet1$].Id = [Sheet3$].Sheet3Id
In SSIS 2005 Excel source, i have set the data access mode to SQL command and typed in the above query to get data from 3 Microsoft excel sheets(sheet1, sheet2, sheet3).
The intention is to get all rows in sheet1 with their corresponding reference values in sheets 2 & 3 but for some strange reason, every row in sheet1 is being returned twice.
I can't seem to figure out what i am doing wrong in query?
|
|
|
|
|
What I normally do i this case is to comment out the joins until I identify which one is causing the duplicates, then I filter to 1 row and go hunting through the data to find the cause.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear,
It happens only when the multiple records are available with the correspondence of [Sheet1$].Id into the [Sheet2$] ([Sheet2$].Sheet2Id) Or [Sheet3$] ([Sheet3$].Sheet3Id).
Please check with the multiple records into the Both the Sheet 2 & 3.
If you dont want to remove the multiple records from those sheets then
Add distinct clause into the SELECT Query.
SELECT DISTINCT
[Sheet1$].Sheet1DetailId,
[Sheet1$].Sheet1Id,
[Sheet2$].Sheet2Id,
LTrim(RTrim([Sheet3$].Mission)) + '-' + CStr(Format('01-' + [Sheet3$].Sheet3Date,"mm/dd/yyyy")) AS Sheet3DateColumn,
[Sheet1$].one,
[Sheet1$].two,
[Sheet1$].three,
[Sheet1$].four,
[Sheet1$].five,
[Sheet1$].six,
[Sheet1$].seven,
[Sheet1$].eight,
[Sheet1$].nine,
[Sheet1$].ten,
LTRIM(RTRIM([Sheet2$].Name)) AS Sheet2Name
FROM (([Sheet1$])
INNER JOIN [Sheet2$] ON [Sheet1$].Id = [Sheet2$].Sheet2Id)
INNER JOIN [Sheet3$] ON [Sheet1$].Id = [Sheet3$].Sheet3Id
|
|
|
|
|
From:
ObjectID - PropertyID - PropertyListValueID
1828 - 41 - 171
1828 - 41 - 170
1828 - 46 - 184
1828 - 47 - 189
To:
ObjectID - 41 - 46 - 47
1828 - 170 - 184 - 189
1828 - 171 - 184 - 189
It's possible? Thanks
|
|
|
|
|
Which version of SQL Server?
You could try PIVOT
|
|
|
|
|
Sql server 2012
pivot without aggregation
|
|
|
|
|
I love the opportunity for self promotion, this article [^]will be of use!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
With PIVOT i get:
ObjectID - 41 - 46 - 47
--------------------------------------
1828 - 171 - 184 - 189
or
ObjectID - 41 - 46 - 47
--------------------------------------
1828 - 171 - 184 - 189
1828 - 170 - NULL - NULL
I must do:
select ObjectID from table where [41]=171 and [46]=184
select ObjectID from table where [41]=170 and [46]=184
|
|
|
|
|
Then you need to prepare your data differently, 46 and 47 do not have values for 170 so you need to add them to the result set.
It is a very weird structure you are asking for where missing values reflect the previous value.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I must filter/select every ObjectID with PropertyID=QueryString value and PropertyListValueID=QueryString value
Initial structure:
+----------+------------+---------------------+
| ObjectID | PropertyID | PropertyListValueID |
+----------+------------+---------------------+
| 1828 | 41 | 171 |
| 1828 | 41 | 170 |
| 1828 | 46 | 184 |
| 1828 | 47 | 189 |
+----------+------------+---------------------+
|
|
|
|
|
hi,
use this table as example:
COD surname name CODCAU from from_time to to_time period
1 ROSSI MARCO 301 19/09/2005 0.00 23/09/2005 0.00 p1
1 ROSSI MARCO 301 09/12/2005 0.00 09/12/2005 0.00 p2
1 BIANCHI FABIO 301 12/01/2004 0.00 16/01/2004 0.00 p1
1 BIANCHI FABIO 301 02/04/2004 0.00 02/04/2004 0.00 p2
1 BIANCHI FABIO 301 02/05/2004 0.00 10/05/2004 0.00 p3
then to pivot:
select *
FROM
(
SELECT surname,name,COD,from+' '+from_time+' '+to+' '+to_time as date,period
FROM [pivot]
) as s
PIVOT
(
max(date)
FOR period IN ([p1],[p2],[p3])
)AS p
|
|
|
|
|
Getting this?
Failed to build the OLAP cubes. Error: Failed to process the Analysis Services database DevelopmentOLAP on the SPSQLCLSTR server. Error: OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [53]. ; 08001.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Project Reporting data source', Name of 'Project Reporting data source'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DevelopmentOLAP', Name of 'Timesheet Line Status' was being processed.
Server: The operation has been cancelled.
The User/Account has dbo access. It creates the database but then doesn't seem to be able to do anything after that?
Ideas?
To err is human to really mess up you need a computer
|
|
|
|
|
Hi All,
I want to select a records from access database depends on where clause as the following :
select ID as found from TransLog where UserID='61' and TransType='0' and TransTime =#28-04-14 08:46:46#
but the error occurred when I use the TransTime =#28-04-14 08:46:46#
the error is:: IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
Please help me ASAP.
Thank you very much
|
|
|
|
|
Where did you enter that query? In an MS Access GUI? In a .Net application which connects to your database? Also: is that the correct DateTime format of that user?
|
|
|
|
|
If you User id is int then no need to put single query. And in TransTime put single quote instead of #
select ID as found from TransLog where UserID=61 and TransType='0' and TransTime ='28-04-14 08:46:46'
Sankarsan Parida
|
|
|
|
|
As title says, I would like to know how to return the primary key(the column is always called id) when I perform an insert statement and the database complains about an unique constraint being violated. All my insert statements are in stored procedures. Would like that same stored procedure to return the value of that primary key column of the row that is being violated.
I find that a cleaner solution than responding to an exception in my application by searching the table for the unique values. This would be ok if it were one or a few tables, but not when you got many.
PS: using MSSQL 2012
|
|
|
|
|
I'm fairly that you already have it; you passed it in didn't you?
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Let me clarify:
Let's say I have a table called address with fields: id, street, streetnumber, busnumber, placeid.
id is the primary key of that table.
Now I insert a row in that table with data in the following fields: street, streetnumber, busnumber, placeid. id is generated by a sequence, but that part doesn't matter. The unique constraint is about those 4 fields.
Suppose I want to insert 2 people who live at the same address, but while this is unknown to me.
Instead of getting a unique constraint warning, I would like to get the id of the record that matches the data I'm trying to insert. I've thought about doing it in the same stored procedure, but I don't know that much about errorhandling inside a stored procedure. I am trying to avoid to having to handle it in my application.
Could you help me out with that?
|
|
|
|
|
Nico Haegens wrote: I want to insert 2 people who live at the same address
Get rid of the unique constraint if it doesn't support the application properly.
Why not check the table first, before trying to insert the record?
You'll never get very far if all you do is follow instructions.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
