> On Oct 12, 2012, at 9:40 AM, Praveen wrote:
> Hello,
> I have below use where
> Employer and EmployerPhone the one emp can have multiple phone number out
> of which at any given point of time there should be
> only one 'is_active' due to concurrency load, emp_phone got multiple
> 'is_active' as 'Y' in emp_phone i want to update the emp_phone table
> records to have only is_active record for a given emp. I tried out query
> and Expression Language to fix this, but i am not lucky enough to fix this.
> There's a lot going on in this question. I want to show you how to get
> the SQL you're looking for, but also there's better ways to do this. I
> apologize for the length here, but this was my thought process as I went
> through your case one step at a time. The "answer" you probably want is at
> the end.
> So first let's deal with the query that is setting the "max" record to
> "Y". I see that you have a separate query doing the "N" part, though there
> are ways to do both at once.
> As far as UPDATE..FROM, I'd recommend reading all the answers to this
> question:
> http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-sta... which will give you a great overview of FROM/JOIN in UPDATE and how all
> over the place it is on different databases. SQLAlchemy supports plain
> UPDATE and recently has added UPDATE...FROM. However it does *not*
> support Oracle's syntax, which is radically different, except for a
> single-column UPDATE, which is already standard SQL.
> Also note SQLite has no support for any UPDATE..FROM syntax at all.
> So below, I'll show you what we have for UPDATE..FROM, but if your target
> is sqlite/mssql/oracle, you're probably going to want to stick with ANSI
> syntax which handles this very easily in any case (the Y and N all at once
> in fact).
> UPDATE..FROM is supported in the SQL expression language as of 0.7. The
> ORM handles this usage as of 0.8 which will have it's first beta releases
> before month's end. UPDATE...FROM ... JOIN is not supported right now,
> but a JOIN can always be stated implicitly in any case.
> Let's talk about your query now. We start with this (note, I corrected
> "phone_num_idn" to read "emp_phone_idn"):
> update emp_phone set is_active='Y'
> from emp_phone b inner join (
> select emp_idn, max(emp_phone_idn) max_emp_phone_idn
> from emp_phone group by emp_idn
> ) a
> on a.max_emp_phone_idn =b.emp_phone_idn
> the JOIN here can be stated implicitly, as in the WHERE clause:
> update emp_phone set is_active='Y'
> from emp_phone b, (
> select emp_idn, max(emp_phone_idn) AS max_emp_phone_idn
> from emp_phone group by emp_idn
> ) a
> WHERE a.max_emp_phone_idn = b.emp_phone_idn
> you also don't need "emp_idn" in the SELECT there, it's just for grouping.
> The ANSI sql version, IMO is a lot easier. It's just this:
> update emp_phone set is_active='Y' where emp_phone_idn IN
> (
> SELECT max(emp_phone_idn) FROM emp_phone group by emp_idn
> )
> Surprisingly, MySQL can't do the ANSI version. Seems you're not on
> MySQL so you should be OK.
> Here's how SQLAlchemy can render these.
> First assume SQLAlchemy 0.8. The ORM there can do UPDATE..FROM like this:
> subq =
> session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')). group_by(EmpPhone.emp_idn).subquery()
> session.query(EmpPhone).filter(EmpPhone.emp_phone_idn ==
> subq.c.max_emp_phone_idn).update({'is_active':'Y'},
> synchronize_session=False)
> The SQL won't be the same as the above, but it's equivalent. I've added a
> test for this case just to make sure. In SQL Server you get this (note, my
> example here uses "documents" since it's from a test case):
> UPDATE documents SET flag=? FROM documents, (SELECT max(documents.title)
> AS title
> FROM documents GROUP BY documents.user_id) AS anon_1 WHERE documents.title
> = anon_1.title
> in MySQL, they want the other FROMs in the UPDATE clause, so you get this:
> UPDATE documents, (SELECT max(documents.title) AS title
> FROM documents GROUP BY documents.user_id) AS anon_1 SET documents.flag=%s
> WHERE documents.title = anon_1.title
> and in Postgresql, the table being updated is not re-stated so we get this:
> UPDATE documents SET flag=%(flag)s FROM (SELECT max(documents.title) AS
> title
> FROM documents GROUP BY documents.user_id) AS anon_1 WHERE documents.title
> = anon_1.title
> The UPDATE..FROM above will not work in SQLite or Oracle.
> Since I'm assuming you're on 0.7, we can adapt the above so that it
> executes via the expression language completely:
> # this is the same
> subq =
> session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')). group_by(EmpPhone.emp_idn).subquery()
> # create an UPDATE directly against the"emp_phone" Table object. Can
> still use the mapped classes
> # for the WHERE clause
> upd
> = emp_phone.update().values(is_active='Y').where(EmpPhone.emp_phone_idn ==
> subq.c.max_emp_phone_idn)
> # execute
> session.execute(upd)
> Next, the ANSI version, which IMO is the simplest, and we can also get the
> whole Y/N thing to happen in one step. We keep working with that same
> subquery, which because it only returns one column can be used in a scalar
> context:
> subq =
> session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')). group_by(EmpPhone.emp_idn).subquery()
> session.query(EmpPhone).filter(EmpPhone.emp_phone_idn.in_(subq)).update({"i s_active":"Y"},
> synchronize_session=False)
> this gives you the SQL (on SQL Server):
> UPDATE emp_phone SET is_active='Y' WHERE emp_phone_idn IN (SELECT
> max(emp_phone_idn) AS max_emp_phone_idn
> FROM emp_phone GROUP BY emp_idn)
> So all of that, is to get your "Y" flag. Let's now see how we can get
> the "Y" and "N" in there all at once. One easy way is to just run the
> same IN query in reverse, to get the "N":
> session.query(EmpPhone).filter(~EmpPhone.emp_phone_idn.in_(subq)).update({" is_active":"N"},
> synchronize_session=False)
> But another, is do to the Y/N all at once using a column-level expression:
> UPDATE emp_phone SET is_active=CASE WHEN (emp_phone_idn in
> (SELECT max(emp_phone_idn) AS max_emp_phone_idn FROM emp_phone GROUP BY
> emp_idn)
> ) THEN 'Y' ELSE 'N' END
> Again, the above works everywhere except MySQL. We can take our subq
> and stick it into a case():
> # note: THIS IS PROBABLY THE ANSWER YOU WANT
> from sqlalchemy import case
> subq =
> session.query(func.max(EmpPhone.emp_phone_idn).label('max_emp_phone_idn')). group_by(EmpPhone.emp_idn).subquery()
> case_stmt = case([(EmpPhone.emp_phone_idn.in_(subq), 'Y')], else_='N')
> session.query(EmpPhone).update({"is_active":case_stmt},
> synchronize_session=False)
> So above, except for on MySQL, you can get the Y/N all in there in one
> shot using ANSI SQL just by putting a subquery into a CASE statement.
> Could anyone suggest me to write query in SqlAlchemy to fix this issue.
> I have also attached sample python file which i tried with SqlLite.
> ####################################
> from sqlalchemy import *
> from sqlalchemy.orm import sessionmaker, mapper
> #Actual use case is with SQL Server 2008 and Oracle
> #uri = "mssql+mxodbc://sa:sa@master"
> #engine = create_engine(uri)
> #Tried with SQL Lite
> engine = create_engine('sqlite:///:memory:', echo=True)
> session = sessionmaker(bind=engine)
> session = session()
> #Define tables....
> metadata = MetaData()
> emp = Table("emp", metadata,
> Column("emp_idn", Integer, primary_key=True,
> autoincrement=True),
> Column("emp_name", String),
> Column("is_active", String),
> )
> emp_phone = Table("emp_phone", metadata,
> Column("phone_no", String),
> Column("emp_phone_idn", Integer, primary_key=True, autoincrement=True),
> Column("emp_idn", Float, ForeignKey('emp.emp_idn')),
> Column("is_active", String))
> metadata.create_all(engine)
> #Define model.
> class Emp(object):
> pass
> class EmpPhone(object):
> pass
> #mapping...
> mapper(Emp, emp)
> mapper(EmpPhone, emp_phone)
> #My goal is to Use Session to create Emp and Emp Phone.
> emp = Emp()
> emp.emp_name = 'Royal'
> emp.is_active = 'Y'
> session.add(emp)
> session.flush()
> emp = Emp()
> emp.emp_name = 'Royal Eng'
> emp.is_active = 'Y'
> session.add(emp)
> session.flush()
> empPhone = EmpPhone()
> empPhone.emp_idn = emp.emp_idn
> empPhone.phone_no = '12345'
> empPhone.is_active = 'Y'
> session.add(empPhone)
> session.flush()
> empPhone = EmpPhone()
> empPhone.emp_idn = emp.emp_idn
> empPhone.phone_no = '67890'
> empPhone.is_active = 'Y'
> session.add(empPhone)
> session.flush()
> #Update all the Duplicate is_active records with 'N' which are duplicted
> per emp_idn
> #There should be only one active phone number per emp
> sub_qry = session.query(EmpPhone.emp_idn).filter(EmpPhone.is_active=='Y')
> sub_qry = sub_qry.group_by(EmpPhone.emp_idn)
> sub_qry = sub_qry.having(func.count(EmpPhone.emp_idn) > 1)
> upd_qry =
> session.query(EmpPhone).filter(EmpPhone.emp_idn.in_(sub_qry)).update({'is_a ctive':
> 'N'}, False)
> session.commit()
> for each in session.query(EmpPhone).all():
> print each.emp_idn, each.phone_no, each.is_active
> ##My Goal is to update the emp_phone records having multiple is_active to
> have
> ##only once is_active as 'Y' based on last loaded record for one employee.
> ##I figured out raw sql, how ever i am not able get this done in SQ Query
> or SQ expression langauge.
> #====Raw SQL====# SQ Query or SQ expression langauge
> #Update emp_phone set is_active='Y'
> #from emp_phone b inner join (
> #Select emp_idn, max(emp_phone_idn) max_emp_phone_idn from emp_phone group
> by emp_idn)
> #a on a.max_emp_phone_idn =b.phone_num_idn
> #====Raw SQL====#
> ####################################
> Thanks in advance,
> Praveen
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/R_pRZBMlVDgJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
> <to_mail.py>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
