-4

this update code shows an error: my need is to update the gender column in two tables by single update query

UPDATE 
    studentbase a, studentprofile b 
SET  
    a.gender='FEMALE', 
    b.gender='FEMALE' 
FROM 
    studentbase a, studentprofile b 
WHERE 
    a.applicationnumber=b.applicationnumber AND 
    a.applicationnumber=12345;
  • 3
    Please only add a tag for the DBMS you are really using. – a_horse_with_no_name Jul 11 '17 at 5:45
  • 3
    You can't do that in most databases... Anyhow, if your data were normalized, you wouldn't have two columns with the same meaning (and values) in two different tables. – joanolo Jul 11 '17 at 6:33
  • 3
    What's wrong with two Updates? – dnoeth Jul 11 '17 at 7:22
1

In Microsoft SQL Server, the straightforward approach probably is to create a trigger script on table [studentbase] which immediately copies the value of [gender] column to table [studentprofile]. This requires moderate care if you haven't done it before.

A less straightforward approach, not particularly better, is to create a view on your joined tables, and an instead-of trigger which performs updates on both tables, one after another, when you perform update on the view. This could address lots of columns in both tables.

A quite eccentric method, which I don't fully have a grip on but I think it'll work, is to let the relationship between [studentbase] and [studentprofile] be on a key of (applicationnumber, gender), and with the words "ON UPDATE CASCADE" put wherever is required - excuse me from looking it up - so that when you change the [gender] in [studentbasic], it's considered to be an alteration in the foreign key value that has to be automatically applied to [studentprofile] as well. I think this is not what the feature is meant to be used for, but the good thing that only you will understand what you've done :-) (This isn't really a good thing unless you want to make it hard for someone to take over your job.)

There may be other ways.

As @joanolo said, the "normal" approach is to have the column in one table, and join to the other table whenever you need to see data from [studentbasic] together with data from [studentprofile]. I'll guess that this isn't done, either because (1) the tables are copied to different places for reference, or (2) users aren't allowed to see both sets of data, or (3) the tables were designed by a manager who mainly does work in Microsoft Excel. I can't comment on 1 and 3, but situation 2 can be addressed in Microsoft SQL by using views again; you give users access to views which show only the data that they are allowed to see, from both tables, without giving them access to the tables - then all that they can do is to use the view.

Or, you may be a student trying to solve a class exercise, or to play practical jokes in the college records. :-)

| improve this answer | |
1

In PostgreSQL, you could do such a thing using a WITH query:

-- A change of mind...
WITH first_update AS
(
    UPDATE 
        studentbase
    SET
        gender = 'FEMALE'
    WHERE
        applicationnumber = 12345
    RETURNING
        applicationnumber, gender
)
UPDATE
    studentprofile
SET
    gender = first_update.gender
FROM
    first_update
WHERE
    studentprofile.applicationnumber = first_update.applicationnumber ;

You take advantage of the fact that PostgreSQL can update a table within a WITH (which, AFAIK, isn't standard SQL), plust the fact that you can have some RETURNING data from it (again, non-standard SQL), and use this data to make a second update.

Notice that you only need to write your parameters (12345, 'FEMALE') once.

You can see the whole setup at dbfiddle here


NOTE: I wouldn't do this kind of things. I'd try to have the data normalized and not redundant. There are very few scenarios where this kind of thing should be done for performance or security reasons.

You use this type of structure (WITH RETURNING ...), in some occasions, when you want to INSERT data into a table, and get a surrogate key (a SERIAL, or a UUID, or some other automatically generated id) that you use to INSERT into a surrogate table.


As an alternative (that can be also implemented in most other databases, with adequate syntax changes) you could write a stored procedure (in PostgreSQL, they're actually User-defined Functions, to do the same thing:

CREATE FUNCTION 
    set_student_gender (in _applicationnumber integer, in _gender text)
RETURNS
    void
AS
$body$
BEGIN
    UPDATE studentbase SET gender = _gender WHERE applicationnumber = _applicationnumber ;
    UPDATE studentprofile SET gender = _gender WHERE applicationnumber = _applicationnumber ;
    RETURN ;
END ;
$body$
LANGUAGE PLPGSQL ;

... and you'd use

SELECT
    set_student_gender(12345, 'FEMALE') ;

dbfiddle here


As a second alternative: go for a trigger, as already suggested by @Robert Carnegie. The principle is the same, only the syntax of trigger functions is different when you use different SQL RDBMS.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.