CODEWITHSUNDEEP
phpmysqlsqlsql-updatemysql-error-1242
wonnie
wonnie

Reputation: 479

Update all rows of a single column from another table

Here are the my tables;

TableP:

Pname | Psize
x | -
y | -
z | -
g | -
h | -

TableS:

Pname | Pdate | Ptext
x | XX.XX.XXXX | aasdhb
x | XX.XX.XXXX | asdbahsbdhasbdh
y | XX.XX.XXXX | ajsdbajsdba
y | XX.XX.XXXX | asndansjdbasd
x | XX.XX.XXXX | asd
z | XX.XX.XXXX | asdasbdhasb
g | XX.XX.XXXX | asdnjasdja
g | XX.XX.XXXX | asndjsabdas
h | XX.XX.XXXX | asndjand
x | XX.XX.XXXX | asdjasndnaksd

As you can notice, i've added a new column (Psize) which stands for storing TableS.Ptext length.

How can i update Psize with related Ptext length?

Here is the my query which is not work :) (Subquery returns more than 1 row)

UPDATE TableP
   set TableP.Psize = (SELECT sum(LENGTH(Ptext)) as length
                         FROM TableS group by Pname)
 where TableP.Pname = TableS.Pname

Thanks in advance.

Upvotes: 0

Views: 523

Answers (2)

Jé Queue
Jé Queue

Reputation: 10637

Another form keeping your subselect:

UPDATE TableP p
set p.Psize = (
    SELECT sum(LENGTH(s.Ptext)) 
    FROM TableS s
    where s.pname = p.pname
)
where exists ( -- just to keep it consistent
  select 1
  from tables s
  where s.pname = p.pname
);

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

Try:

UPDATE TableP p
inner join (
    SELECT Pname, sum(LENGTH(Ptext)) as length 
    FROM TableS 
    group by Pname
) s on p.Pname = s.Pname
set p.Psize = s.length

Upvotes: 1

Related Questions