CODEWITHSUNDEEP
postgresqlamazon-redshift
RustyShackleford
RustyShackleford

Reputation: 3677

How to change update statements into insert statements while maintaining SET command from UPDATE command?

As the title suggests, I need to change my update and join statements into insert statements. How I would do this while incorporating SET from the UPDATE statements?

Update statement:

UPDATE tableA
SET    A = tableB.A
FROM   tableB
JOIN   tableC ON tableB.C = tableC.C
WHERE  tableC.D = tableA.D

Upvotes: 0

Views: 44

Answers (2)

Ilgorbek Kuchkarov
Ilgorbek Kuchkarov

Reputation: 95

Generally, it is good practice to include all the column names in the INSERT statement. And you can remove WHERE clause by JOINing tableA to tableC. But just the simple answer for your question would be:

INSERT INTO tableA (<col name you want to update>)
VALUES (SELECT <one value> FROM tableB JOIN tableC ON  tableB.C = tableC.C JOIN tableA ON tableC.D = tableA.D
);

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Your tables are empty, thus tableC.D = tableA.D wouldn't provide anything.

May it be you are looking for something like this?:

INSERT INTO tableA (A)
SELECT DISTINCT tb.A
FROM tableB tb
JOIN tableC tc ON tb.C = tc.C

Or perhaps including the D column:

INSERT INTO tableA (A, D)
SELECT DISTINCT tb.A, tc.D
FROM tableB tb
JOIN tableC tc ON tb.C = tc.C

Note the use of DISTINCT here to dispense duplicate records.

Upvotes: 2

Related Questions