Re: Update with correlated sub query - Database Discussion Boards

spankyleo12313-Sep-12 23:55

13-Sep-12 23:55

Hi,

I want to update the column TID with the top most value of TID as long as MID is the same number.

I want to do this (table after update)

PID SID TID MID RID
3 0 0 4 1
3 NULL 0 4 2
3 NULL 0 4 3
3 NULL 0 4 4
3 NULL 0 4 5
3 NULL 0 4 6
3 NULL 0 4 7
3 4 4 8 1
3 NULL 4 8 2
3 NULL 4 8 3
3 NULL 4 8 4

Below is the actual table before update.

PID SID TID MID RID
3 0 0 4 1
3 NULL NULL 4 2
3 NULL NULL 4 3
3 NULL NULL 4 4
3 NULL NULL 4 5
3 NULL NULL 4 6
3 NULL NULL 4 7
3 4 4 8 1
3 NULL NULL 8 2
3 NULL NULL 8 3
3 NULL NULL 8 4

Any suggestion/solutions deeply appreciated.

Thanks

Re: Database updates

Shameel14-Sep-12 1:31

Shameel

14-Sep-12 1:31

Assuming your DB is SQL Server, you can try this (just out of mind, not tried though)

SQL

UPDATE MyTable A SET A.TID = (SELECT MAX(B.TID) FROM MyTable B WHERE A.MID = B.MID)

Re: Database updates

spankyleo12314-Sep-12 2:12

spankyleo123

14-Sep-12 2:12

Thanks for your help.

I have solved it by creating a view of all 'Not NULL' (top most) rows . Then created a stored proc to loop through the view and update the TID. A long procedure though but it did work.

Cheers !

Update with correlated sub query

David Mujica14-Sep-12 3:16

David Mujica

14-Sep-12 3:16

I think you want to do something like this ...

update table A
set tid = 
(select max(tid) 
from TableA
Group By mid)

Check these for references:
http://msdn.microsoft.com/en-us/library/ms177523.aspx

http://data.bangtech.com/sql/sql_update_with_correlated_subquery.htm

Re: Update with correlated sub query

spankyleo12314-Sep-12 3:43

spankyleo123

14-Sep-12 3:43

Hi thanks for your solution and links,

But your query returned an error.

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Re: Update with correlated sub query

Jörgen Andersson14-Sep-12 8:55

Jörgen Andersson

14-Sep-12 8:55

That's because he forgot a small but important condition.
Try this:

SQL

update table A as a1
set tid = 
(select max(tid) 
from TableA as A2
Where A1.mid = A2.mid
Group By mid)

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Update with correlated sub query

David Mujica15-Sep-12 15:11

David Mujica

15-Sep-12 15:11

Thanks for the refinement to my SQL. Sometimes when you don't have the actual data to test with, you forget some stuff.

Re: Update with correlated sub query

Jörgen Andersson16-Sep-12 1:05

Jörgen Andersson

16-Sep-12 1:05

David Mujica wrote:
Sometimes when you don't have the actual data to test with, you forget some
stuff.

Tell me about it. Roll eyes | :rolleyes:

That's almost something I can put in my CV.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

SUM Problem in MSSQL

Basak Oner11-Sep-12 23:41

Basak Oner

11-Sep-12 23:41

Hi everybody,
I have some numbers. Numbers are
(
0
234,9
-27
0
-55,9
0
0
-59
0
100,72
-26
0
-25,72
0
0
0
-82
0
0
0
0
0
0
-134)

I want to this.
SELECT SUM(FIELDX) FROM TBLX

but result is returning (-2,8421709430404007E-14)
actually resutl is 0 (Zero)
How can i solve this problem?

Thanks.

Başak Öner

Re: SUM Problem in MSSQL

Eddy Vluggen12-Sep-12 0:16

Eddy Vluggen

12-Sep-12 0:16

Basak Oner wrote:
actually resutl is 0 (Zero)

Not if you're a computer, then it's "almost 0". Try rounding the result.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SUM Problem in MSSQL

Basak Oner12-Sep-12 0:27

Basak Oner

12-Sep-12 0:27

sorry I don't understand your message.
I need your detailed explanation.

Thanks.

Re: SUM Problem in MSSQL

Eddy Vluggen12-Sep-12 0:31

Eddy Vluggen

12-Sep-12 0:31

Basak Oner wrote:
sorry I don't understand your message.

Try the ROUND function. This is due to how computers work and how they calculate. You know what a fractional number is, but they do not exist within a computer.

Basak Oner wrote:
I need your detailed explanation.

You're not in school anymore. There's an explanation here[^], good luck.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SUM Problem in MSSQL

Basak Oner12-Sep-12 3:58

Basak Oner

12-Sep-12 3:58

Thanks for your free time.

Re: SUM Problem in MSSQL

Eddy Vluggen12-Sep-12 6:00

Eddy Vluggen

12-Sep-12 6:00

You actually *read* it?

Ehr.. you're welcome, and yes, well done Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SUM Problem in MSSQL

Jörgen Andersson12-Sep-12 1:00

Jörgen Andersson

12-Sep-12 1:00

You can try to change the datatype of the field.

Check if SELECT SUM(cast(FIELDX as numeric(5,2)) FROM TBLX works.
In your real data you naturally need to adjust the precision and scale of the numeric field to fitting values.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

SQL Seconds to Day, Hour, Minute, Second

ahsansharjeel11-Sep-12 13:49

ahsansharjeel

11-Sep-12 13:49

Hi Experts

I have a query which needs to return the second in Day, Hour, Minute, Second format.

The below code works fine when its less than a day, but does not work, when the value in second is greater than a day

PRINT Convert(VarChar, DateAdd(S, 86400, 0), 108)

86400 is exactly a day and it returns 00:00:00

Can someone modify it and show me the result something like this

1:00:00:00.

Thanks

Re: SQL Seconds to Day, Hour, Minute, Second

Mycroft Holmes11-Sep-12 14:24

Mycroft Holmes

11-Sep-12 14:24

Take a look at MODULO from BOL!

SELECT 38 / 5 AS Integer, 38 % 5 AS Remainder ;<br />

Use a case statement to detect if it is over 86399 and apply the appropriate formula. If you use it in multiple places then create your own UDF.

Never underestimate the power of human stupidity
RAH

Re: SQL Seconds to Day, Hour, Minute, Second

cbeglobal12-Sep-12 1:46

cbeglobal

12-Sep-12 1:46

Try the option, CONVERT(VARCHAR(10),'fieldname',112)

Re: SQL Seconds to Day, Hour, Minute, Second

Mycroft Holmes12-Sep-12 12:36

Mycroft Holmes

12-Sep-12 12:36

Try replying to the op next time, he will not get notification when you reply to me!

Never underestimate the power of human stupidity
RAH

Re: SQL Seconds to Day, Hour, Minute, Second

Hitesh R6-Nov-12 0:21

Hitesh R

6-Nov-12 0:21

try this :

SQL

DECLARE @DurationSeconds INT
DECLARE @DurationDays INT
SET @DurationSeconds = 62110
SET @DurationDays = @DurationSeconds /86400

Select CASE WHEN @DurationDays > 0 THEN
Convert(VarChar,@DurationDays)+ ':'+
Convert(VarChar, DateAdd(S, (@DurationSeconds-(@DurationDays*86400)),0), 108)
ELSE
Convert(VarChar, DateAdd(S, @DurationSeconds, 0), 108)
END

TableAdapters results differ from Stored Procedure results, C# - SQL 2000

lowhitaker10-Sep-12 11:15

lowhitaker

10-Sep-12 11:15

Using Sql 2000 and C#.

I have a stored procedure where I pass in my identifier and use it to select a set of rows from several tables. When I execute this SP in query analyzer it works great, returning only the rows that do not exist in @Data. When executed in C# it is returning the entire set of data relevant to that identifier.

I'm at a loss on this. I've spent hours changing the SP around trying to get different results. I've tried both temp tables and var tables with the same results. Again, it always works in QA but never in C#.

Any thoughts? I've shortened some of the repetitive data from this query to save on screen space (and your eyes) but let me know if there is anything else i can show that could help.

Also, here's the weird part. I have a near exact duplicate of this query that works in both C# and QA. The only difference is that #Chas is populated via a select statement that returns a range of values instead of just being populated by the passed in value. I know i don't have to insert this value into #Chas but instead could just compare directly against it. It was like this just to make it as identical to the original as possible since the original works.

SQL

USE [B_D_DashPanel]
GO
/****** Object:  StoredProcedure [dbo].[sp_BD_PermConflicts]    Script Date: 09/10/2012 15:55:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
@Chassis_Search nvarchar(50)
AS


Declare @Data TABLE(
Chassis nvarchar(50),
ItemC nvarchar(50),
Location nvarchar(50),
Panel nvarchar(13),
Part nvarchar(50)
)

Create TABLE #Chas (
Chassis_No nvarchar(50)
)

set nocount on

insert into #Chas (Chassis_No) values (@Chassis_Search)


insert into @Data (Chassis, ItemC, Location, Panel, Part)
select distinct WorkPlacementorder.salesorderno, workplacementorder.itemcode, workplacementorder.location, workplacementorder.panel, 
itembompn.partnumber1
from #Chas,  workplacementorder 
join itembompn 
on workplacementorder.itemcode = itembompn.itemcode
inner join chassisdb.dbo.tblopenjit 
on workplacementorder.salesorderno = chassisdb.dbo.tblopenjit.chassisnbr 
where chassisdb.dbo.tblopenjit.chassisnbr like #Chas.Chassis_No and chassisdb.dbo.tblopenjit.itemid = itembompn.partnumber1
and WorkPlacementorder.salesorderno like #Chas.Chassis_No



SELECT b_d_Dashpanel.dbo.workplacementorder.ItemCode, b_d_Dashpanel.dbo.items.itemdescription
FROM #Chas, B_D_DashPanel.dbo.WorkPlacementOrder join b_d_Dashpanel.dbo.items on b_d_Dashpanel.dbo.workplacementorder.itemcode = b_d_Dashpanel.dbo.items.itemcode
where salesorderno = Chassis_No and b_d_DashPanel.dbo.workplacementorder.itemcode not in (select itemc from @Data)



drop table #Chas

Re: TableAdapters results differ from Stored Procedure results, C# - SQL 2000

Mycroft Holmes10-Sep-12 14:19

Mycroft Holmes

10-Sep-12 14:19

Logic dictates that there is a difference between @Chassis_Search passed in as a nvarchar and the format stored in the table. Can you do a select from itembompn where field = @Chassis_Search. It may be a character set incompatibility.

Try changing the field to varchar!

Also never use sp_ to prefix your procedures, this forces SQL Server to search all the system procedures as well as your own - it can be a real performance hit.

Never underestimate the power of human stupidity
RAH

Re: TableAdapters results differ from Stored Procedure results, C# - SQL 2000

Jörgen Andersson11-Sep-12 0:37

Jörgen Andersson

11-Sep-12 0:37

I would go one step further, don't prefix.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: TableAdapters results differ from Stored Procedure results, C# - SQL 2000

Mycroft Holmes11-Sep-12 1:10

Mycroft Holmes

11-Sep-12 1:10

I still prefix views and functions I gave up on procs many versions ago.

Never underestimate the power of human stupidity
RAH

Re: TableAdapters results differ from Stored Procedure results, C# - SQL 2000

Michael Potter11-Sep-12 4:06

Michael Potter

11-Sep-12 4:06

Remove [#Chas] from the entire query. Change your where statements to:

SQL

chassisdb.dbo.tblopenjit.chassisnbr LIKE @Chassis_Search 
WorkPlacementorder.salesorderno LIKE @Chassis_Search 

salesorderno = @Chassis_Search

I question the last where clause - why isn't it also a LIKE? Seems like it would fail when wildcards are used.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.