October 23, 2016 at 2:17 am
Hello,
I am using SQL Server 2014. I have two table the first one is Table_A and Table_B.
I want to copy row(s) from Table_B into Table_A based on a condition. The first condition is a sequence number "OYHSSQA" and the second is Type "OYNMTP" I want to have the row from Table_B copy to Table_A. The sequence number "OYHSSQA" will appear twice, but the Type "OYNMTP" will have a unique letter ("A" or "S") I want to have the combine condition for example:
From Table_B
OYHSSQA OYNMTP
1578 A
1578 S
If Table_A does not have both rows then I want Table_B to insert the rows into Table_A.
I want both rows to appear in Table_A, below is my SQL query, but I unable to get any results. Any tips will be appreciated. Thank you in advance
SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA
FROM TABLE_B
WHERE NOT EXISTS (SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA
FROM Table_A
WHERE TABLE_B .OYHSSQ = Table_A.OYHSSQ AND TABLE_B .OYNMTP = Table_A.OYNMTP)
October 23, 2016 at 4:39 am
If I understood correctly then this should do the job
😎
SELECT
TB.OYHSSQ
,TB.OYNMTP
,TB.OYNAME
,TB.OYADD1
,TB.OYADD2
,TB.OYCITY
,TB.OYSTAT
,TB.OYZIP4
,TB.OYCNTC
,TB.OYPHAR
,TB.OYPHXX
,TB.OYPH04
,TB.OYPHEX
,TB.OYFL18
,TB.OYADD3
,TB.OYCTID
,TB.OYCTCD
,TB.OYADD4
,TB.OYPHNA
FROM TABLE_B TB
LEFT OUTER JOIN Table_A TA
ON TB.OYHSSQ = TA.OYHSSQ
AND TB.OYNMTP = TA.OYNMTP
WHERE TA.OYHSSQ IS NULL;
Make certain you have an index on Table_A(OYHSSQ,OYNMTP)
October 23, 2016 at 11:04 am
Thanks Eirikur Eiriksson I will give it a try. I appreciate your help.
October 23, 2016 at 12:05 pm
alex_martinez (10/23/2016)
Thanks Eirikur Eiriksson I will give it a try. I appreciate your help.
You are most welcome, and please ping back if you have any problems
😎
October 24, 2016 at 3:17 am
I believe, there is also nothing wrong in query you built. Why is it not working?
SELECT
OYHSSQ,
OYNMTP,
OYNAME,
OYADD1,
OYADD2,
OYCITY,
OYSTAT,
OYZIP4,
OYCNTC,
OYPHAR,
OYPHXX,
OYPH04,
OYPHEX,
OYFL18,
OYADD3,
OYCTID,
OYCTCD,
OYADD4,
OYPHNA
FROM TABLE_B
WHERE NOT EXISTS (
SELECT
OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2,
OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR,
OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3,
OYCTID, OYCTCD, OYADD4, OYPHNA
FROM Table_A
WHERE TABLE_B .OYHSSQ = Table_A.OYHSSQ AND TABLE_B .OYNMTP = Table_A.OYNMTP
)
Regards
VG
October 24, 2016 at 9:02 am
>> I want to copy row(s) from Table_B into Table_A based on a condition. The first condition is a sequence number "OYHSSQA" and the second is Type "OYNMTP" I want to have the row from Table_B copy to Table_A. The sequence number "OYHSSQA" will appear twice, but the Type "OYNMTP" will have a unique letter ('A' or 'S') I want to have the combine condition for example: <<
Did you read the instructions for posting on the form? Where is your DDL? It is very hard to program from a narrative, especially a bad one. Why do you think those column names are clear, precise and meaningful?
But more than that the whole goal of databases (not just RDBMS!) is to reduce redundancy, not increase it the way you are doing. This condition should be computed at query time or in a view. What you are doing is a way we used to do things with punch cards (yes, I am that old). Make a copy of the card and put it in the other deck. I see you use short names for columns, just like we did with Fortran I , which could only hold six letters in a name. You do not follow ISO 11179 rules. And there even in upper case letters, just like the punch cards.
Post some DDL, as per form rules, and will try to really help you otherwise were just going to have to start guessing at what you meant. A sure sign of this problem is when someone begins a reply with "If I understand you correctly, then.."
Another problem you have is you do not understand how the EXISTS() predicate works. Why did you put a column list in the select clause of this predicate? Think about it. 😉
RDBMS is based on logic, and in logic. The foundation is a principal called the Law of Identity ("to be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all."). This means there is no such thing as a generic "type", generic "name", etc. this is enforced by the ISO 11179 naming rules.
You describe a constraint in a narrative, instead of enforcing it in code. An SQL programmer would have written in his DDL with UNIQUE(oyhssqa, oynmtp).
If you have this unique this constraint, then you can write a simple view to find the rows that have both {'A', 'S'} in a simple view.
CREATE VIEW Foobar
AS
SELECT * -- needs real column list
FROM Punchcards
GROUP BY oyhssqa -- oyster_highschool_square_a? I cannot figure out your data element names
HAVING COUNT(*) = 2;
If Table_A does not have both rows then I want Table_B to insert the rows into Table_A.
I want both rows to appear in Table_A, below is my SQL query, but I unable to get any results. Any tips will be appreciated. Thank you in advance
SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA
FROM TABLE_B
WHERE NOT EXISTS
(SELECT OYHSSQ, OYNMTP, OYNAME, OYADD1, OYADD2, OYCITY, OYSTAT, OYZIP4, OYCNTC, OYPHAR, OYPHXX, OYPH04, OYPHEX, OYFL18, OYADD3, OYCTID, OYCTCD, OYADD4, OYPHNA
FROM Table_A
WHERE TABLE_B .OYHSSQ = Table_A.OYHSSQ
AND TABLE_B.OYNMTP = Table_A.OYNMTP)
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
October 24, 2016 at 10:19 pm
CELKO
I am just amazed in how long you took the time to write this post, like what I am doing NOW.
Old man like you who are playing the "Post Police" I just don't like your attitude.
Funny part about all this is I worked with a lot of senior programmers I enjoy working with and are great mentors.
They are nothing like you! Next time you see my reply just move on. I don't need to have you reply period! or give me a lecture.
"Seriously" go pick on a junior staff member "if you haven't already", people like you pick on the little guys too much "really".
Just move on go on to another post and whine to the next person.
Help and comments like yours I don't need or nobody needs. Don't mask how you trying to help! I know your type.
There was only two data columns I was really interested "OYHSSQA", which I explained it's a sequence number and the other "OYNMTP" is a unique lettering column. I don't think I need to explain anymore to you, especially when some else guessed it correctly.
A top notch person like yourself with all your experiences should able to guess. If you don't have enough data from a post move on.
Also, The kind person who helped me guessed "correctly" I was able to use it and guess what it works! and that's all I need from a forum. Not a whining reply. I will make sure I will submit a DLL. Fortunately I really don't use this forum too much and probably not in the near future.
Seriously take a Stress Pill.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply