2

I want to run the following query:

CREATE TABLE dummy AS
SELECT x, y, z
FROM table1
WHERE y!=0

then I want to do the following:

if z column has a non-null entry do:
1. copy the content of z into A
2. drop z
SELECT x,A
FROM Dummy 

What the second query essentially tries to do is that copy those rows of z into A which have no missing values. My question is how can we write query 2 and if there is a way to merge these two queries into one for optimisation?

Any help/advice much appreciated.

  • Do you mean (update/delete & select) in one single query? And where column a comes from? – McNets Nov 5 '18 at 16:36
  • Yes, I guess. This is what I mean. Column A is created based on the condition if z column has non-null entries. – T.H. Nov 5 '18 at 16:39
  • There's no way to Update and Drop in the same query, no. – Mark Sinkinson Nov 5 '18 at 16:50
3

Since you aren't using column y, you can ignore it in your first query. Then, you can simplify this with a single statement:

SELECT 
    x, 
    A = z
FROM table1
WHERE y!=0 and z is not null
  • Thanks for this but I am sorry I did not understand the solution. I think I was not clear in my question. I am trying to create a new column 'A' which has the contents of column z only when there are non-null entries in z (i.e z column has no missing data). – T.H. Nov 5 '18 at 16:48
  • @T.H. then the simplified edit should work – scsimon Nov 5 '18 at 16:55

Your Answer

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.