SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Tough query for newbie Me
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pwvailla
Starting Member

31 Posts

Posted - 09/28/2010 :  07:45:40  Show Profile  Reply with Quote
table A
orderno
buscode
busunit
custname
engno

table b
orderno
buscode
busunit
custname
engno
engstart
engfinish

table c
orderno
projectno
project_name

table d
projectno
project_status

SQL problem:
Table A is used to update / insert records into table B provided the orderno exists in table c and the table D project_status = 'A' for an active project.

I know you will connect the tables A + B with orderno+buscode and tables A + C with orderno and table C + D by projectno.

Do you create a separate UPDATE routine and an INSERT? What would they look like with the JOINS?

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 09/28/2010 :  13:46:34  Show Profile  Visit jcelko's Homepage  Reply with Quote
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Yes, you need one insertion statement for each target table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

pwvailla
Starting Member

31 Posts

Posted - 09/30/2010 :  07:02:09  Show Profile  Reply with Quote
<edit by tkizer>inappropriate post</edit>

Edited by - tkizer on 09/30/2010 13:42:00
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
942 Posts

Posted - 09/30/2010 :  12:18:03  Show Profile  Reply with Quote
2005 version

INSERT INTO tableB (orderno,buscode,busunit,custname,engno)
SELECT	a.orderno,buscode,busunit,custname,engno
FROM	tableA a JOIN tableC c
		ON a.orderno=c.orderno
		JOIN tableD d
		ON c.projectno=d.projectno
		AND d.project_status = 'A'
		
UPDATE	b
SET		b.busunit = a.busunit
		,b.custname = a.custname
		,b.engno = a.engno
FROM	tableB b JOIN tableA a
		ON b.orderno = a.orderno
		AND b.buscode = a.buscode


2008 version

MERGE	tableB AS B
USING	(SELECT	a.orderno,a.buscode,a.busunit,a.custname,a.engno,c.projectno,c.project_name,d.project_status
		FROM	tableA a JOIN tableC c
				ON a.orderno=c.orderno
				JOIN tableD d
				ON c.projectno=d.projectno
				AND d.project_status = 'A'
		) AS A (orderno,buscode,busunit,custname,engno,projectno,project_name,project_status)
		ON
		(B.orderno = A.orderno AND B.buscode = A.buscode)
WHEN NOT MATCHED THEN
	INSERT(orderno,buscode,busunit,custname,engno)
	VALUES(a.orderno,a.buscode,a.busunit,a.custname,a.engno)
WHEN MATCHED THEN
UPDATE
SET		B.busunit = A.busunit
		,B.custname = A.custname
		,B.engno = A.engno;


Sorry if i'm wrong...im also a newbie


Hope can help...but advise to wait pros with confirmation...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000