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

    studentbase a, studentprofile b 
    studentbase a, studentprofile b 
    a.applicationnumber=b.applicationnumber AND 
  • 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

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 | |

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

-- A change of mind...
WITH first_update AS
        gender = 'FEMALE'
        applicationnumber = 12345
        applicationnumber, gender
    gender = first_update.gender
    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:

    set_student_gender (in _applicationnumber integer, in _gender text)
    UPDATE studentbase SET gender = _gender WHERE applicationnumber = _applicationnumber ;
    UPDATE studentprofile SET gender = _gender WHERE applicationnumber = _applicationnumber ;
    RETURN ;

... and you'd use

    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.