none
Could I do this with less code?

    Question

  • create table #jim (name varchar(100), createdon datetime)
    -- drop table #jim

    create table #jim2 (name varchar(100), createdon datetime)
    -- drop table #jim2

    insert into #jim
    select a.new_printedpublicationname, b.CreatedOn from [dbo].[new_iphcprintedpublicationinventoryExtensionBase] a
    join [dbo].[new_iphcprintedpublicationinventoryBase] b on a.[new_iphcprintedpublicationinventoryId] = b.[new_iphcprintedpublicationinventoryId]
    where new_printedpublicationname like 'annual%'

    insert into #jim2
    select name, createdon as 'Current Publication' from #jim
    where createdon = (select MAX(createdon) as 'Current Publication' from #jim)
    -- select * from #jim2

    begin tran
    update [dbo].[new_iphcprintedpublicationinventoryExtensionBase]
    set [new_CurrentPublication] = 1
    where [new_PrintedPublicationName] COLLATE DATABASE_DEFAULT in  (select name from #jim2)
    --rollback
    --commit

    Friday, July 05, 2013 11:27 PM

Answers

  • Those are two really interesting solutions from Vedran and RSingh!

    But I still get a little nervous updating "from" complex conditions, I've never figured out just how it is SQL decides what it should do, so following more KISS rules I might fiddle the code like this.

    Josh

    /*
    find the oldest publication in the queue
    and set the currentpublication flag for all with the same name
    */
    create table new_iphcprintedpublicationinventoryBase
    (
    	new_iphcprintedpublicationinventoryId int identity(1,1) primary key,
    	CreatedOn datetime
    );
    create table new_iphcprintedpublicationinventoryExtensionBase
    (
    	new_pk int identity(1,1) primary key,
    	new_iphcprintedpublicationinventoryId int,
    	new_PrintedPublicationName varchar(max),		
    	new_CurrentPublication bit
    );
    --
    declare @topid int, @topname varchar(max);
    --
    with mypubs as
    (
    	select 
    		a.new_iphcprintedpublicationinventoryId as topid,
    		a.new_printedpublicationname as topname,
    		CreatedOnRank = RANK() OVER(ORDER BY b.CreatedOn DESC)
    	from [dbo].[new_iphcprintedpublicationinventoryExtensionBase] a
    		inner join [dbo].[new_iphcprintedpublicationinventoryBase] b 
    			on a.[new_iphcprintedpublicationinventoryId] = b.[new_iphcprintedpublicationinventoryId]
    	where a.new_printedpublicationname like 'annual%'
    )
    select
      @topid = topid,
      @topname = topname
    from mypubs
    where CreatedOnRank = 1;  
    --
    update [dbo].[new_iphcprintedpublicationinventoryExtensionBase]
    set [new_CurrentPublication] = 1
    where [new_PrintedPublicationName] = @topname;
    --
    go

    I'm still guessing a bit at the key structure of the two tables ... shouldn't have rushed into this on a Saturday!  But perhaps it gives the right ideas for coding anyway.



    • Edited by JRStern Saturday, July 06, 2013 4:51 PM fighting with editor
    • Marked as answer by James R Traub Tuesday, July 09, 2013 6:05 PM
    Saturday, July 06, 2013 4:30 PM
  • UPDATE command has two different syntaxes. The one I tend to use is in fact a SELECT statement turned into UPDATE. For example, in my original update command:

    UPDATE t SET t.someColumnName = 1
    FROM
    ( ...
    ) t
    WHERE...

    there are FROM and WHERE parts just like in SELECT statements, only UPDATE..SET.. is aded above FROM. That is very convenient because one can easily convert that update into select to see the rows instead of updating them. Just comment-out "UPDATE ... SET... " and put "SELECT *" there:

    SELECT * --UPDATE t SET t.someColumnName = 1
    FROM
    ( ...
    ) t
    WHERE...

    That is for manual check of the data that will be updated. You could also make that a two-step process and select ID's into temp table, and then do UPDATE in second step to rows with that IDs. That obviously requires transaction and handling rollback in case of error. It is not my preferred method because it is more complicated than single update command.

    Note that query can return more than one row if they share the same max CreatedOn date (I suppose that is what you wanted from reading your design). Because of that, it is not good to store the name into a variable, since a (scalar) variable can hold only one value. I don't think it is very good idea to store the name for an update, because name has no unique constraint thus is not unique. new_PK is unique (primary key), and using something unique is the only good choice.

    • Marked as answer by James R Traub Tuesday, July 09, 2013 6:05 PM
    Saturday, July 06, 2013 10:25 PM

All replies

  • Can you explain in English what do you want to do?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Saturday, July 06, 2013 12:39 AM
  • You didn't provide complete example (definition of joined tables are missing). The answer is certainly yes. You could use a CTE for example and avoid explicit transaction.

    Or you could use something like this:

    UPDATE t
    SET t.new_CurrentPublication = 1
    FROM
    (
        select a.UniqueID, a.new_CurrentPublication,
            CreatedOnRank = RANK() OVER(ORDER BY b.CreatedOn DESC)
        from [dbo].[new_iphcprintedpublicationinventoryExtensionBase] a
            join [dbo].[new_iphcprintedpublicationinventoryBase] b
                on a.[new_iphcprintedpublicationinventoryId] = b.[new_iphcprintedpublicationinventoryId]
        where a.new_printedpublicationname like 'annual%'
    ) t
    WHERE t.CreatedOnRank = 1

    I cannot verify that works without your table definitions. Also, what's the point of setting some rows to 1, if previously marked rows (now not current) are not reset back to 0 ?

    Saturday, July 06, 2013 12:42 AM
  • Refer the below sample,

    create table XBASE (name varchar(100), createdon datetime, iid int,cpublication int)
    INSERT INTO XBASE VALUES('monthly',GETDATE()-19,1,null)
    INSERT INTO XBASE VALUES('annual',GETDATE()-12,2,null)
    INSERT INTO XBASE VALUES('annual',GETDATE()-11,3,null)
    DROP TABLE XBASE
    create table IID (name varchar(100), createdon datetime, iid int)
    INSERT INTO IID VALUES('xyz',GETDATE()-14,4)
    INSERT INTO IID VALUES('annual',GETDATE()-12,2)
    INSERT INTO IID VALUES('annual',GETDATE()-11,3)
    DROP TABLE IID
    ----------------- final query
    ;WITH CTE AS (
    	SELECT IID.NAME,IID.CREATEDON,ROW_NUMBER() OVER (ORDER BY IID.createdon DESC) AS ROWID 
    	FROM XBASE INNER JOIN IID ON XBASE.IID=IID.IID
    	WHERE XBASE.[NAME]='annual'
    )
    ------------------
    UPDATE XBASE
    SET cpublication = 1
    WHERE [NAME] COLLATE DATABASE_DEFAULT  IN (SELECT [NAME] FROM CTE WHERE CREATEDON = (SELECT CREATEDON FROM CTE WHERE ROWID=1))
    -------------------
    SELECT * FROM XBASE
    SELECT * FROM IID


    Regards, RSingh

    Saturday, July 06, 2013 2:50 AM
  • Those are two really interesting solutions from Vedran and RSingh!

    But I still get a little nervous updating "from" complex conditions, I've never figured out just how it is SQL decides what it should do, so following more KISS rules I might fiddle the code like this.

    Josh

    /*
    find the oldest publication in the queue
    and set the currentpublication flag for all with the same name
    */
    create table new_iphcprintedpublicationinventoryBase
    (
    	new_iphcprintedpublicationinventoryId int identity(1,1) primary key,
    	CreatedOn datetime
    );
    create table new_iphcprintedpublicationinventoryExtensionBase
    (
    	new_pk int identity(1,1) primary key,
    	new_iphcprintedpublicationinventoryId int,
    	new_PrintedPublicationName varchar(max),		
    	new_CurrentPublication bit
    );
    --
    declare @topid int, @topname varchar(max);
    --
    with mypubs as
    (
    	select 
    		a.new_iphcprintedpublicationinventoryId as topid,
    		a.new_printedpublicationname as topname,
    		CreatedOnRank = RANK() OVER(ORDER BY b.CreatedOn DESC)
    	from [dbo].[new_iphcprintedpublicationinventoryExtensionBase] a
    		inner join [dbo].[new_iphcprintedpublicationinventoryBase] b 
    			on a.[new_iphcprintedpublicationinventoryId] = b.[new_iphcprintedpublicationinventoryId]
    	where a.new_printedpublicationname like 'annual%'
    )
    select
      @topid = topid,
      @topname = topname
    from mypubs
    where CreatedOnRank = 1;  
    --
    update [dbo].[new_iphcprintedpublicationinventoryExtensionBase]
    set [new_CurrentPublication] = 1
    where [new_PrintedPublicationName] = @topname;
    --
    go

    I'm still guessing a bit at the key structure of the two tables ... shouldn't have rushed into this on a Saturday!  But perhaps it gives the right ideas for coding anyway.



    • Edited by JRStern Saturday, July 06, 2013 4:51 PM fighting with editor
    • Marked as answer by James R Traub Tuesday, July 09, 2013 6:05 PM
    Saturday, July 06, 2013 4:30 PM
  • This schema was designed by someone without any idea of ISO-11179  rules, or basic data modeling. Since the moron did not include keys, there are no tables in this posting. Next, we never have two tables with the same structure in a valid schema. A table models a set and the set occurs once and only once in a schema! 

    “name” of nothing in particular is not a precise, clear data element. Hey, I think is the name of the dog of the uncle of the programmer! Why not? We do not have a generic creation_date (note that I re-wrote it to ISO-11179 rules and not that “createdon” adverbial phrase you had); we have a precise publication_date. This is the nature of publications. 

    That prefix “new” is a status code, not a role or an attribute. But it is not even a good status code-- we can use the publication dates to clearly define "new"!  Likewise, “printed” is also an attribute value (“media_type”, if I remember correctly) Also, not a lot of names of any kind are 100 characters long. Do think perhaps our moron just made up that number without any research? 

    Then one error I love is alphabetical aliases! Think about how this screws up maintaining the code: 

     Publication_Inventory_Extension_Base AS A -- wow! what a name for a table! 

    This comes from the tape drives in the first mini-computers, which became A:, B:, C:, etc on personal computers. Files were read from the tape drives and had to be referenced by the drive names. You are writing code based on a 50+ year old model of EDP! 

    I earn my living fixing SQL this bad, so I need diagnostics. A doctor would look for a fever, bleeding, skin rashes, etc. to help him find why the patient is sick. I look for this kind of code to tell what kind of errors are being made (OO models forced into SQL? Sequential Tape files forced in SQL? Mimicking paper forms? Etc?). People make systematic errors!  When you learn Turkish, you will use English word order and it does not work; but it tells the teacher why you are messing up. We can then make corrections. 

    This is foundations, not fancy stuff! This is just why the SQL is wrong. It gets worse. Your data model is a disaster. Have you ever done a library system? 

    Then you do not know how publication work. There is the ISSN, as the industry standard identifier. Would identify a car without a VIN? A book without an ISBN? A bank account without an IBAN? 

    The truly awful, non_RDBMS block of code was using assembly language flags in SQL!! We find the current publication by looking at the publication date, not with a 1950's bit flag!! 

    Post some DDL (you were rude and failed to do so) and we will try to repair this mess. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, July 06, 2013 5:00 PM
  • UPDATE command has two different syntaxes. The one I tend to use is in fact a SELECT statement turned into UPDATE. For example, in my original update command:

    UPDATE t SET t.someColumnName = 1
    FROM
    ( ...
    ) t
    WHERE...

    there are FROM and WHERE parts just like in SELECT statements, only UPDATE..SET.. is aded above FROM. That is very convenient because one can easily convert that update into select to see the rows instead of updating them. Just comment-out "UPDATE ... SET... " and put "SELECT *" there:

    SELECT * --UPDATE t SET t.someColumnName = 1
    FROM
    ( ...
    ) t
    WHERE...

    That is for manual check of the data that will be updated. You could also make that a two-step process and select ID's into temp table, and then do UPDATE in second step to rows with that IDs. That obviously requires transaction and handling rollback in case of error. It is not my preferred method because it is more complicated than single update command.

    Note that query can return more than one row if they share the same max CreatedOn date (I suppose that is what you wanted from reading your design). Because of that, it is not good to store the name into a variable, since a (scalar) variable can hold only one value. I don't think it is very good idea to store the name for an update, because name has no unique constraint thus is not unique. new_PK is unique (primary key), and using something unique is the only good choice.

    • Marked as answer by James R Traub Tuesday, July 09, 2013 6:05 PM
    Saturday, July 06, 2013 10:25 PM
  • I always forget which ranking function is which, if you use row_number() then there is no problem with multiples.  Well, there is a problem but it will be given an arbitrary solution, and the problem is in the original example code, too, I preserved it!

    The "update from" syntax is a Microsoft extension to standard SQL if I recall correctly, and I often find it ambiguous, even when SQL Server does not.

    As I read the original request (perhaps incorrectly?) it anticipates finding multiple entries matching the name, so that's meant as a feature not a bug.

    I left off the collation details.

    Josh

    ps - on second thought, the original code *can* handle two different titles at the same time, so by assigning the title to a scalar (even with row_number) I have introduced a further limitation after all.  but given the original code, I'm not certain if that's a good or bad thing.



    • Edited by JRStern Sunday, July 07, 2013 8:45 PM second thought revisited
    Sunday, July 07, 2013 2:20 AM
  • I want to update a bit field in one table based on a createdon date in a second table. The bit marks the row or "Publication" as current. In theory as each "Publication" is added it will have the most current createdon date. This same bit field is used as a predicate in maintaining inventory.

    The tables can be joined by new_iphcprintedpublicationinventoryId. as it turns out each "Publication" and therefore new_iphcprintedpublicationinventoryId is unique though.

    Thanks you!! Your answer and professional manner is appreciated!!


    Monday, July 08, 2013 5:24 PM
  • I want to update a bit field in one table based on a createdon date in a second table. The bit marks the row or "Publication" as current. In theory as each "Publication" is added it will have the most current createdon date. This same bit field is used as a predicate in maintaining inventory.

    The tables can be joined by new_iphcprintedpublicationinventoryId. as it turns out each "Publication" and therefore new_iphcprintedpublicationinventoryId is unique though.

    Hope that helps. If not I can take it from here.

    Thanks again!! Your answer and professional manner is appreciated!!

    if previously marked rows (now not current) are not reset back to 0 ? I have not started that piece yet.

    Monday, July 08, 2013 5:25 PM
  • I want to update a bit field in one table based on a createdon date in a second table. The bit marks the row or "Publication" as current. In theory as each "Publication" is added it will have the most current createdon date. This same bit field is used as a predicate in a where clause maintaining inventory.

    The tables can be joined by new_iphcprintedpublicationinventoryId. as it turns out each "Publication" and therefore new_iphcprintedpublicationinventoryId is unique though.

    Hope that helps. If not I can take it from here.

    Your answer and professional manner is appreciated!!


    Monday, July 08, 2013 5:26 PM
  • I want to update a bit field in one table based on a createdon date in a second table. The bit marks the row or "Publication" as current. In theory as each "Publication" is added it will have the most current createdon date. This same bit field is used as a predicate in a where clause maintaining inventory.

    The tables can be joined by new_iphcprintedpublicationinventoryId. as it turns out each "Publication" and therefore new_iphcprintedpublicationinventoryId is unique though.

    Hope that helps. If not I can take it from here.

    Thank you very much!! Your answer and professional manner is appreciated!!

    Monday, July 08, 2013 5:27 PM
  • I want to update a bit field in one table based on a createdon date in a second table. The bit marks the row or "Publication" as current. In theory as each "Publication" is added it will have the most current createdon date. This same bit field is used as a predicate in a where clause maintaining inventory.

    The tables can be joined by new_iphcprintedpublicationinventoryId. as it turns out each "Publication" and therefore new_iphcprintedpublicationinventoryId is unique though.

    Thanks Josh!! Your answer and professional manner is appreciated!!


    Monday, July 08, 2013 5:29 PM
  • That was my problem. All publications are unique so finding the MAX createdon date was difficult.

    Thanks again!! Your answer and professional manner is appreciated!!


    Monday, July 08, 2013 5:35 PM
  • Although I appreciate your suggestions your demeanor needs improving. No one wants to be referred to as a "moron" simply because they are a beginner. Being an "expert" or an author should not allow you to also be a condescending a**hole.

    Please refrain from replying to my posts in the future. Thank you.

    Wednesday, July 10, 2013 5:16 PM