SQL script replacing column's data

Question

I have a PostgreSQL database and I want to make a script that replaces the data of one column of the table A with the data of an other tables column. I've written this PL/PgSQL function:

BEGIN;

    CREATE TEMPORARY TABLE tmp_table (id bigint PRIMARY KEY,
        registrationnumber character varying(255));
    INSERT INTO tmp_table
    select id,registrationnumber from tableB;

    for d in tmp_table loop
        update TABLEA set registrationnumber=d.id where 
            registrationnumber=d.registrationnumber;
        return next d;
    end loop;

END;

What is going wrong with my script?

N West · Accepted Answer · 2012-07-31 15:33:17Z

2

There's no reason to do this in a loop - let the database engine do it for you.

 UPDATE tablea 
  SET registrationnumber = tableb.id
  FROM tableb
 WHERE tablea.registrationnumber = tableb.registrationnumber;

select * from tablea;

See this SQLFiddle:

http://sqlfiddle.com/#!1/1281b/1

Note that you are implicitly casting a varchar value to a bigint. If any of these varchar values don't cast correctly, the statement will fail.

answered Jul 31, 2012 at 15:33

N West

6,79428 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL script replacing column's data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related