UPDATE the Columns of the Rows of the SELECT output

Question

TABLE fruit

name      family       ordered           ordered_on
apple      tree         false             null
banana     tree         false             null
mango      tree         false             null

TABLE basket

name      family       ordered           ordered_on
apple      tree         false             null
banana     tree         false             null

I have a Select query comparing two tables using Except. Now I need to change few columns of Table fruit of row mango.

I tried the below query and it updated all the rows in the fruits table:

UPDATE fruits 
   SET ordered='true', ordered_on = '08/14/2019' 
  FROM (SELECT LEFT name, family 
          FROM fruits 
        EXCEPT 
        SELECT name, family 
          FROM basket) AS subquery

I expect the output:

Table fruit:

mango      tree         true             08/14/2019

Barbaros Özhan · Accepted Answer · 2019-08-14T20:37:06.953

1

You can use with..as clause composed of your query in it, and match through family and name columns :

with t0 as
( 
 select name, family from fruits except select name, family from basket
)
update fruits t1
   set ordered='true', ordered_on = '2019-08-14'
  from t0
 where t1.name = t0.name and t1.family = t0.family;

Demo

edited Aug 14 '19 at 20:37

answered Aug 14 '19 at 20:32

Barbaros Özhan

59,113
10
31
55

What if I have to cast the data type of Col name on Table fruit to text. – MrKarma4u Aug 14 '19 at 22:20
Nothing changes, you can try it on the Demo @MrKarma4u – Barbaros Özhan Aug 15 '19 at 05:54

forpas · Answer 2 · 2019-08-14T20:44:59.030

You can do it with NOT EXISTS:

update fruits f
set ordered = true, ordered_on = '2019-08-14'
where not exists (
  select 1 from basket
  where name = f.name and family = f.family
)

See the demo.
Results:

| name   | family | ordered | ordered_on |
| ------ | ------ | ------- | -----------|
| apple  | tree   | false   |            |
| banana | tree   | false   |            |
| mango  | tree   | true    | 2019-08-14 |

UPDATE the Columns of the Rows of the SELECT output

TABLE fruit

TABLE basket

I expect the output:

Table fruit:

2 Answers2