CODEWITHSUNDEEP
mysqlsqlpostgresqlsql-update
Alejandro Dapena Sanz
Alejandro Dapena Sanz

Reputation: 37

UPDATE A COLUMN WITH DIFFERENT VALUES IN ROWS

I want to update ColumnX of TableA with the values of ColumnY in TableB.

This two tables have in common the atrribute id. Is it possible, when I try an UPDATE code I get

Subquery returns multiple rows

The subquery looks something like this:

UPDATE TableA 
SET ColumnX = (SELECT ColumnY FROM TableB WHERE tableA.id=tableA.id);

Upvotes: 0

Views: 228

Answers (4)

Himanshu
Himanshu

Reputation: 3970

Just change the name in your where clause condition after = as you have same table name resulting into multiple results to TableB.id or try below 

         UPDATE TableA A
         Join TableB B
         On
         A.id= B.id
        SET A.ColumnX = B.ColumnY

Upvotes: 0

Dani
Dani

Reputation: 937

You can use joins like when you are selecting rows, for example:

UPDATE TableA, TableB set TableA.ColumnX=TableB.ColumnY WHERE TableA.id=TableB.id

Upvotes: 0

Avocado
Avocado

Reputation: 911

your where clause is currently using tableA.id=tableA.id, which will be true for every row. Try:

UPDATE TableA 
SET ColumnX = (SELECT ColumnY FROM TableB WHERE tableB.id=tableA.id);

Upvotes: 1

zip
zip

Reputation: 4061

Try:

UPDATE TableA 

    SET ColumnX = (SELECT ColumnY FROM TableB WHERE tableA.id=tableB.id);

And make sure that SELECT ColumnY FROM TableB WHERE tableA.id in (SELECT id FROM TableB ) returns 1 value

Upvotes: 1

Related Questions