CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Mano
Mano

Reputation: 21

Insert values using select query

I tried lot to achieve this but no luck. Let me explain I have table A and moving the data to another called table B, while moving I need to update col2 values, when table A col1 equal to col3 then update col2 = col3(Matching column col3). if values is not found then we need to search next row, if values is matched then need to update col2 = col3 if not matched then need to check next row in same table if we didn’t get matching values then we need to update col2 as null or empty

Ex.

Insert into table B (col1, col2, col3)
    select col1, col2= 
        (Select col3 from table A where col1 =col3),col3 
    from Table A

It is not working because sub query will return multiple values because of select query(col2=select……)

Table A

col1 col2 col3
111 null 222
222 null 333
444 null 111

Table B should be like this col3 value should match witch col1 value in any of the rows in the table and then we have to pick matching col3 value and update in col2. hope this make sense

col1 col2 col3
111 333 222
222 null 333
444 222 111

Please help me to solve the issue

Upvotes: 1

Views: 72

Answers (1)

Jim Demitriou
Jim Demitriou

Reputation: 603

Not entirely sure of the logic, but you can use an update with a join to reference the value from another table.

UPDATE TABLE B
SET B.COL2 = A.COL3
FROM A
WHERE A.COL1 = B.COL3;

Of course, this is assuming there are no duplicate values in COL3 ... may need to see more data to fully understand the use case and scenario you're dealing with.

Upvotes: 0

Related Questions