3

I have the following tables in Oracle:

Table 1 Table 2 AllCustomers ProductCode Customers 5200000 ABC 15265 DEF 156890

In Oracle, I want to join them both, like this:

Table 3 ProductCode Customers AllCustomers ABC 15265 5200000 DEF 156890 5200000

How can I join these tables? As you can see, they do not have a key field to join. I just need to populate a third column in the new table with the same value in it, which would be the one from AllCustomers. Thanks in advance!

Improve this question
2
  • 1
    What do you need if Table1 has more than one customer in it? (More than one row?) What you are requesting is called a Cartesian join and the Oracle syntax is select * from table2 cross join table1 - but if Table 1 has multiple rows, the result will repeat Table 2 several times, once for each row in Table 1. Commented Feb 16, 2018 at 15:53
  • @mathguy Thanks for your answer. Table 1 will always contain 1 row, so I think the cross join would be fine. Commented Feb 16, 2018 at 15:59

2 Answers 2

Reset to default
4

Maybe you can try Cross join

SELECT t2.*,t1.* FROM Table1 t1 CROSS JOIN Table2 t2
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

You can achieve your goal without a join, like so:

SELECT ProductCode, Customers, (SELECT AllCustomers FROM Table1 WHERE ROWNUM = 1) FROM Table2
Improve this answer

2 Comments

To "Join two tables" doesn't mean that Table 2 has one more column, and to "update" that table. The OP has explicitly labeled the result of the join as Table 3. So - why an UPDATE?
Sorry, I missed the "Table 3" part. I will update my answer. Thanks!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.