Solved

Upadte SQL table based on records in another table

Posted on 2014-02-28
40
223 Views
Last Modified: 2014-03-01
In SQL server 2008 express.

I need to lookup each instance of startdate from tbl1 in tbl2 and insert the downtime code from tabl1 into tbl2 only where the downtime in tbl2 is not null.

tbl_1

ID     StartDate      StopDate       StopTime    DowntimeCode

1       2014-01-01   2014-01-02         24                     3
2       2014-01-02   2014-01-03         31                     6
3


tbl_2

ID     StartDate      StopDate       StopTime    DowntimeCode
         2014-01-01                                                          3
         2014-01-01                                                          3
         2014-01-02                                                          6

Thankyou very much for your assistance.
0
Comment
Question by:SweetingA
  • 24
  • 8
  • 7
  • +1
40 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 39895887
update tbl_2
set downtimecode = t1.downtimecode
from tbl_2 t2
inner join tbl_1 t1 on t1.startdate = t2.startdate
where t2.downtimecode is not null
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39895905
Just make sure you have indexes on the two tables for t1.startdate = t2.startdate columns and if you have large number of rows to UPDATE then do it in batches - you can use SET ROWCOUNT for that
0
 

Author Comment

by:SweetingA
ID: 39896054
I added it to a stored procedure and executed it.  Runs fine bur does not update table 2?
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39896062
based on the records you showed, there's no update needed.  tbl2 already has the downtime codes that are in tbl1
0
 

Author Comment

by:SweetingA
ID: 39896099
no, i want to input the downtime codes from tbl1, they won't already be there
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39896104
can you post some sample records from each table to show what should be getting updated?
0
 

Author Comment

by:SweetingA
ID: 39896129
Attached is a small extract
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39896130
no attachment...
0
 

Author Comment

by:SweetingA
ID: 39896147
oops, sorry
extract.xls
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39896165
I don't see any reason this shouldn't work to update tbl2.  Looks like the tbl2 record with startdate 2014/01/26 14:50 should get its downtimecode set to null

update tbl_2
set t2.downtimecode = t1.downtimecode
from tbl_2 t2
inner join tbl_1 t1 on t1.startdate = t2.startdate
where t2.downtimecode is not null
0
 

Author Comment

by:SweetingA
ID: 39896175
I think i seem to have caused some confusion

table 2 should be updating table 1 where startdate in table 2 is not null and startdate in table 1 is null
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39896183
that makes a little difference, lol...

update tbl_1
set t1.downtimecode = t2.downtimecode
from tbl_1 t1
inner join tbl_2 t2 on t2.startdate = t1.startdate and t2.downtimecode is not null
where t1.downtimecode is null
0
 

Author Comment

by:SweetingA
ID: 39896195
I get an error on the SET line

The multi-part identifier "t1.DowntimeCode" could not be bound.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39896201
update tbl_1
set downtimecode = t2.downtimecode
from tbl_1 t1
inner join tbl_2 t2 on t2.startdate = t1.startdate and t2.downtimecode is not null
where t1.downtimecode is null
0
 

Author Comment

by:SweetingA
ID: 39896220
runs successfully but still does not update table 1
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 39896224
update MachineData
set downtimecode = Temptbl_Stops.downtimecode
from MachineData
inner join Temptbl_Stops on Temptbl_Stops.startdate = MachineData .startdate and Temptbl_Stops.downtimecode is not null
where MachineData .downtimecode is null
0
 

Author Comment

by:SweetingA
ID: 39896238
Same result, runs successfully but updates nothing
0
 

Author Comment

by:SweetingA
ID: 39896301
Is there anything else i can try?
0
 

Author Comment

by:SweetingA
ID: 39896792
This works in a select command to get the same results but i need an update command.

SELECT     dbo.tbl_MachineData.ID, dbo.tbl_MachineData.Date, dbo.tbl_MachineData.ShiftType, dbo.tbl_MachineData.ShiftID, dbo.tbl_MachineData.Machine,
                      dbo.tbl_MachineData.ProductCode, dbo.tbl_MachineData.Batch, dbo.tbl_MachineData.CavitationStd, dbo.tbl_MachineData.CavitationAct,
                      dbo.tbl_MachineData.CycleTimeStd, dbo.tbl_MachineData.CycleCount, dbo.tbl_MachineData.ScrapCount, dbo.tbl_MachineData.CycleTimeAct,
                      dbo.tbl_MachineData.StopCount, dbo.tbl_MachineData.RunStatus, dbo.tbl_MachineData.StartDate, dbo.tbl_MachineData.StopDate,
                      CASE WHEN dbo.tbl_MachineData.DowntimeCode IS NOT NULL
                      THEN dbo.tbl_MachineData.DowntimeCode ELSE dbo.temptbl_Stops.DowntimeCode END AS DowntimeCode
FROM         dbo.tbl_MachineData LEFT OUTER JOIN
                      dbo.temptbl_Stops ON dbo.tbl_MachineData.StartDate = dbo.temptbl_Stops.StartDate

You have to excuse me as i am a novice but thanks for the help so far
0
 

Author Comment

by:SweetingA
ID: 39896794
Tried this, no error but still no update when running stored procedure

UPDATE dbo.tbl_MachineData
SET DowntimeCode = (SELECT CASE WHEN dbo.tbl_MachineData.DowntimeCode IS NOT NULL THEN dbo.tbl_MachineData.DowntimeCode ELSE dbo.temptbl_Stops.DowntimeCode END AS DowntimeCode
FROM dbo.tbl_MachineData
LEFT OUTER JOIN dbo.temptbl_Stops ON dbo.tbl_MachineData.StartDate = dbo.temptbl_Stops.StartDate)
END
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:SweetingA
ID: 39896805
I really don't understand why this code doesn't work, i have atached both full tables

UPDATE tbl_MachineData
SET DowntimeCode = temptbl_Stops.DowntimeCode
FROM tbl_MachineData
INNER JOIN temptbl_Stops
ON tbl_MachineData.StartDate = temptbl_Stops.StartDate
extract.xls
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39896856
Hi,

UPDATE d
SET DowntimeCode = s.DowntimeCode
-- select d.DowntimeCode, s.DowntimeCode
FROM dbo.tbl_MachineData$ d
INNER JOIN dbo.temptbl_Stops$ s
	ON d.StartDate = s.StartDate 
;

Open in new window


What I changed:
I aliased the tables
I used the alias on everything
Due to the import from excel there is a $ at the end of the tables.
I specified the schema for each table.
I inserted a select and commented it out.
The update specifies the table alias and not the table itself. This change might be the difference between it working and not.

Suggestion
Run the select, then do the update, then run the select again. Notice that the nulls got updated.

I find this a useful technique - embedding the select - as during testing/development the select can be run to see if the joins and where clauses etc are all working, and then run the update.

HTH
  David

PS Short answer is that the last piece of code you posted should have worked for you. The piece above that is a bit dodgy with sub-selects that aren't needed imho and an end that is weird.
0
 

Author Comment

by:SweetingA
ID: 39896898
I will try it but i don't import from excel, i just pasted into excel so the experts could see what my tables look like
0
 

Author Comment

by:SweetingA
ID: 39896910
Again it executes without errors but updates nothing - i am totally stumped

If i create a select as above it works fine, is there no way to get that select into an update?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39896912
Hi,

Of course I don't expect you to do the import from excel - I was explaining why the table names where different in my code to your code. I expect you'll have to edit the table names - removing the $ - and then it will work for you.

Regards
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39896919
Hi,

Does the select show this:
DowntimeCode	DowntimeCode
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	1
NULL	1
NULL	1
NULL	2
NULL	2

Open in new window


or this:
DowntimeCode	DowntimeCode
3	3
3	3
3	3
3	3
3	3
3	3
3	3
3	3
1	1
1	1
1	1
2	2
2	2

Open in new window


How do you know that it updates nothing? It works for me with the data you provided.

Regards
  David
0
 

Author Comment

by:SweetingA
ID: 39896946
Hi David,

Because my downtime column in table_MachineData is still all NULLs

I have never ran the select, you have to understand i am a novice so you may have to explain exactly how to run it, do i just uncomment it?  where will the results show?

Thanks for the patience
0
 

Author Comment

by:SweetingA
ID: 39896950
I am not sure if this was right but i....

Commented out the update and set lines
Uncommented the select line
Ran the select
Did the opposite
Ran the update
Did the opposite
Ran the select

Same result, all run but table never changes

I have to go out now for a while but thanks for your help so far, maybe you will be back on line sometime later.
0
 

Author Comment

by:SweetingA
ID: 39897190
Anyone got any idea what i may be doing wrong?
0
 

Author Comment

by:SweetingA
ID: 39897564
Hi David,

The result of the SELECT is similar to the first table

DowntimeCode     Exp1
NULL      3        
NULL      3        
NULL      3        
NULL      3        
NULL      3        
NULL      3        
NULL      3        
NULL      3        
NULL      1        
NULL      1        
NULL      1        
NULL      2        
NULL      2
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39897574
Hi,



This is the query I posted.
By highlighting or selecting everything from the select, this is what will run when clicking on the execute button.

Otherwise with nothing selected the entire query will run and the select line will be commented out.

HTH
  David
0
 

Author Comment

by:SweetingA
ID: 39897585
This is really bizzare.....

If i run this code in a view, it works perfectly

UPDATE d
SET DowntimeCode = s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
ON d.StartDate = s.StartDate

If i run the same code in a stored procedure, it does not update tbl_MachineData, stored procedure is attached

USE [OEE]
GO
/****** Object:  StoredProcedure [dbo].[stp_UpdateTemptbl_Stops]    Script Date: 03/01/2014 18:38:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[stp_UpdateTemptbl_Stops]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

UPDATE d
SET DowntimeCode = s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
ON d.StartDate = s.StartDate

END

I really just need a little guidance as to why this is the case and what is the basic thing i am doing wrong?
0
 

Author Comment

by:SweetingA
ID: 39897591
I undertstand, the question i had was where do i see the results of the select on the screen, when in a view its at the bottom but where does it print to when running a stored procedure?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39897602
Hi,

>> When I run this in a view ...
To clarify, a view in sql is an object like a procedure is an object - it is a script that is stored in the database in effect.

Now a view can have one select statement - there can be sub-selects and correlated subqueries, but overall it is one statement.

Now when you execute the query and see the results in the bottom of the screen, that is the default. I like to use different tabs myself.

The code you ran as a procedure is the code to create or alter the procedure. You then need to execute that like this

execute [dbo].[stp_UpdateTemptbl_Stops]

HTH
  David
0
 

Author Comment

by:SweetingA
ID: 39897628
Where do i put that?

Normally i execute stored proedures from vb express

In this case i have been simply pressing the execute ! at the top of the screen, does this not really execute the procedue?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39897641
Hi,

The code that you execute

create procedure dbo.SomeProcedure ...

merely creates an object within the database.

In SSMS you can navigate to it under the database in question - Programmability | Stored Procedures.

So what you are executing is the script to create the procedure.

The line I wrote above should be in another query and you need to execute that script (by clicking on the above button)

So we have two different T-SQL queries - one to create or alter the procedure, and the other to execute it. (Of course you can use my technique as per the above and simply comment out one and highlight the other in the same script, but lets not get too confusing.)

No idea how you are executing the procedure from VB - and at this stage, since you are still developing the procedure, executing from SSMS (instead of VB) is likely the better option.

HTH
  David
0
 

Author Comment

by:SweetingA
ID: 39897702
Hi David,

You have been incredibly patient and for sure deserve the points but can you list the complete copde in your execute query because mine still does not give the correct result (or any result for that matter).

I have only been using SQL for a couple of weeks so i am seriously struggling with some basics.

I have tried to execute the stored procedure via vb which i do with other stored procedures and it again does not error but does not update.
0
 

Author Comment

by:SweetingA
ID: 39897708
Ah its ok, i used execute to and then the ! symbol and that worked fine....

Sorry for being a pest.
0
 

Author Closing Comment

by:SweetingA
ID: 39897710
Very patient person, thanyou very much for the help
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39897724
Hi,

No worries.

If you're coming from a programming background and procedural environments, it takes some getting your head around the function environment that is a rdbms and SQL.

That is, in vb you describe step-by-step what to do. In SQL you say what you want the results to be and SQL figures out how to do it.

And then there is the set vs row thing as well.

So what I did.
I have on my workstation a database called Experts Exchange which is what I use for answering questions here.

In SSMS I right-clicked on ExpertsExchange and selected tasks and import data. Stepping through that wizard got your data into the database. I wondered when the wizard said that it imported a number of rows from MachineData, so I took a look and deleted the null rows.

Although out of sequence, I'm going to rename the tables

I then edited your update script.

I then created a reset update and commented it out.

Here is my complete code.
--
use ExpertsExchange
go

select *
from [dbo].[tbl_MachineData]
;

/*
delete [dbo].[tbl_MachineData]
where
	ID is null

*/

UPDATE d
SET DowntimeCode = s.DowntimeCode
-- select d.DowntimeCode, s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
	ON d.StartDate = s.StartDate 
;

/*
-- reset
UPDATE d
SET DowntimeCode = null
-- select d.DowntimeCode, s.DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
	ON d.StartDate = s.StartDate 
;

*/

Open in new window


In executing this code, I often select just the pieces of the script that I want to execute as per a previous screen-shot.

Now if you need that packaged as a stored procedure
use ExpertsExchange
go

-- =============================================
-- Create stp_UpdateTemptbl_Stops
-- =============================================
-- Creating the store procedure
if exists (select name 
	   from   sysobjects 
	   where  name = N'stp_UpdateTemptbl_Stops'
	   and 	  type = 'p')
    drop procedure dbo.stp_UpdateTemptbl_Stops 
go

create procedure dbo.stp_UpdateTemptbl_Stops
-- =============================================
-- stp_UpdateTemptbl_Stops
-- =============================================
--

-- Author	David Todd
-- etc
--

			
-- =============================================
as
	set nocount on
        ;

	UPDATE d
	SET DowntimeCode = s.DowntimeCode
	-- select d.DowntimeCode, s.DowntimeCode
	FROM dbo.tbl_MachineData d
	INNER JOIN dbo.temptbl_Stops s
		ON d.StartDate = s.StartDate 
	;

go

-- =============================================
-- Grant execute rights to Public for the store procedure
-- =============================================
grant execute on dbo.stp_UpdateTemptbl_Stops to Public 
go

-- =============================================
-- example to execute the store procedure
-- =============================================
execute dbo.stp_UpdateTemptbl_Stops
go

Open in new window


Running this select before
select d.DowntimeCode as Data_DowntimeCode, s.DowntimeCode as Stops_DowntimeCode
FROM dbo.tbl_MachineData d
INNER JOIN dbo.temptbl_Stops s
	ON d.StartDate = s.StartDate 
;

-- results
Data_DowntimeCode	Stops_DowntimeCode
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	3
NULL	1
NULL	1
NULL	1
NULL	2
NULL	2

Open in new window


executing the create procedure/execute procedure script from above gives this result
Command(s) completed successfully.

Open in new window


and then re-running the above select
Data_DowntimeCode	Stops_DowntimeCode
3	3
3	3
3	3
3	3
3	3
3	3
3	3
3	3
1	1
1	1
1	1
2	2
2	2

Open in new window


Note in the create procedure script that it checks and deletes the existing version of the procedure. The script uses go as the batch terminator. That is, the script isn't submitted in one chunk, but in multiple batches to SQL.

Note that in the selects I've aliased the columns, so when looking at the results it is more obvious which column is which.

HTH
  David
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now