How to use update from select in postgreSQL?

Question

update users
set users.przetwarzanie = historia_panel.zmiana_z
from historia_panel 
where 
        users.user_number like historia_panel.user_number and
        historia_panel.zmienna_zmieniana like '%przetwarzanie%' and
        historia_panel.przyczyna like '%użytkownika%';

And the error is:

The error that is in console:
ERROR:  column "users" of relation "users" does not exist
LINE 2: set users.przetwarzanie = historia_panel.zmiana_z

In this query i want to update users table with values from historia_panel table with specified conditions. I tried many different ways but none of examples that i found on the internet works.

Actualy i have no idea what I'am doing wrong...

Mark Adelsberger · Accepted Answer · 2017-01-20 15:02:02Z

2

The error would suggest that you aren't allowed to qualify the column names in the set clause; and indeed you shouldn't need to since you already said update users

Whether that's the only problem I can't say at a quick glance, but that is what the error message is telling you; so try changing set users.przetwarzanie = historia_panel.zmiana_z to just set przetwarzanie = historia_panel.zmiana_z and see what you get

answered Jan 20, 2017 at 15:02

Mark Adelsberger

46.6k4 gold badges42 silver badges60 bronze badges

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

3 Comments

A. Dziedziczak Over a year ago

ERROR:  column "przetwarzanie" is of type integer but expression is of type character varying LINE 2: set przetwarzanie = historia_panel.zmiana_z

that's what i got. I think the problem is with the update users. It should be in one FROM statement or something like this. And still i have no idea how to solve this error but thanks for answer ;)

Mark Adelsberger Over a year ago

Not sure why you think the problem is with the update users; that's not at all what the error message says. It says you're assigning character data to an integer. First of all that means processing got far enough that the problem you keep wanting to look for (overall problem with query structure) doesn't exist. Secondly, you need to know your data. The error arises because pg doesn't want to be put in the position of guessing what to do if it finds itself being told to assign a value like 'HELLO' to a column that only accepts numbers, as assigning a varchar to an integer could do

A. Dziedziczak Over a year ago

Yeah you are right. ;) I changed it reinterpret type of historia_panel.zmienna_z to int by adding `::int:

A. Dziedziczak · Accepted Answer · 2017-01-20 15:27:43Z

0

The problem was with incompatible typles between two columns from different tables. I added line set przetwarzanie = historia_panel.zmiana_z::int and now it works.

answered Jan 20, 2017 at 15:27

A. Dziedziczak

3234 silver badges13 bronze badges

2 Comments

user330315 Over a year ago

The real question is: why are you storing numbers in a varchar column?

A. Dziedziczak Over a year ago

I'am using varchar because historia_panel is a history log of changes made by users on their accounts. Users can change for example username and this change is stored in historia_panel. I'm not the one who created this table but I'm the one who need to restore some changes from historia_panel and apply these changes to users table. ;)

Collectives™ on Stack Overflow

How to use update from select in postgreSQL?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related