Add row from table B to table A based on conditions

I'm using python with sqlite. I have two datatables, table A and table B. I want to join the rows of table B with A on the following conditions:

If I have the following two tables

Table A Table B
2 4 1 3 2 2 2 1
2 2 2 1 2 4 1 3
4 5 2 4 2 4 1 3
1 4 2 5 2 4 1 3
2 4 1 3 5 3 2 3


I want to have the following table

Table A + B
2 2 2 1
2 4 1 3
2 4 1 3
2 4 1 3
4 5 2 4
1 4 2 5
5 3 2 3


Question: Is it possible to achieve this with an sql query? If so; how?

Edit: While the answers have proven very helpful they don't solve the entire question.

SELECT valB1, valB2, valB3, valB4 FROM B
WHERE NOT EXISTS(SELECT valA1, valA2, valA3, valA4
FROM A
WHERE A.valA1 = B.valB1
AND A.valA2 = B.valB2
AND A.valA3 = B.valB3
AND A.valA4 = B.valB4)


will return rows that are not in A and that are in B. If a row occurs 3 times in B and only 2 times in A it will not return one instance of that row, which is what I would need in order to bump up the occurence of that row in table A.

The query

SELECT col1, col2, col3, col4
FROM TableA
EXCEPT
SELECT col1, col2, col3, col4
FROM TableB
UNION ALL
SELECT col1, col2, col3, col4
FROM TableB


doesn't work because if a row less in B than in A, I'll end up with the amount of that row that were in B, instead the amount that were in A.
Thanks!

2 Answers
2

Same columns, and stich them together in one resultset?

Sounds like a UNION ALL and EXCEPT can be used here.

SELECT col1, col2, col3, col4
FROM TableA
EXCEPT
SELECT col1, col2, col3, col4
FROM TableB
UNION ALL
SELECT col1, col2, col3, col4
FROM TableB


Test on db<>fiddle here

But it has the disadvantage that it will result with the amount of duplicate rows as found in TableB.

And if you'd be able to use a Sqlite version (3.25+) that finally supports the window functions? Reference. Then using ROW_NUMBER can fix the problem of the "duplicates TableA > duplicates TableB" issue:

SELECT col1, col2, col3, col4
FROM
(
SELECT col1, col2, col3, col4,
row_number() over (partition by col1, col2, col3, col4 order by (select 0)) as rn
FROM TableA
EXCEPT
SELECT col1, col2, col3, col4,
row_number() over (partition by col1, col2, col3, col4 order by (select 0))
FROM TableB
UNION ALL
SELECT col1, col2, col3, col4, 0
FROM TableB
) q
ORDER BY col1, col2, col3, col4


test

In older versions, the ROW_NUMBER window function can be mimicked.

SELECT col1, col2, col3, col4
FROM
(
SELECT a.col1, a.col2, a.col3, a.col4,
(select count(*) from TableA a2
where a2.col1 = a.col1
and a2.col2 = a.col2
and a2.col3 = a.col3
and a2.col4 = a.col4
and a2.rowid >= a.rowid) as rn
FROM TableA a
EXCEPT
SELECT b.col1, b.col2, b.col3, b.col4,
(select count(*) from TableB b2
where b2.col1 = b.col1
and b2.col2 = b.col2
and b2.col3 = b.col3
and b2.col4 = b.col4
and b2.rowid >= b.rowid)
FROM TableB b
UNION ALL
SELECT col1, col2, col3, col4, 0
FROM TableB
) q
ORDER BY col1, col2, col3, col4


Test on db<>fiddle here

SELECT col1, col2, col3, col4 FROM TableA EXCEPT SELECT col1, col2, col3, col4 FROM TableB UNION ALL SELECT col1, col2, col3, col4 FROM TableB

For If a row occurs in B and not in A, add it to A., based on Shawn's comment:

INSERT INTO A (valA1, valA2, valA3, valA4)
SELECT valB1, valB2, valB3, valB4 FROM B
WHERE NOT EXISTS(SELECT valA1, valA2, valA3, valA4
FROM A
WHERE A.valA1 = B.valB1
AND A.valA2 = B.valB2
AND A.valA3 = B.valB3
AND A.valA4 = B.valB4)


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.