Thursday, August 2, 2012

Netezza update based on a table join

I have worked with SQL Server for more than 8 years, and I'm used to writing queries like this when I'm trying to update data based on query involving table joins:

update tableA
set col1 = 0
from tableA A
join tableB B on A.id = B.bid
where b.name = 'x'

So after years of writing this kind of sql, which comes so natural to me, imagine my surprise when I moved to Netezza and wrote a query like this to update some data and I get an error message that says:

ERROR [HY000] ERROR:  Update canceled: attempt to update a target row with values from multiple join rows

Reading the message, it's trying to tell me that based on my join, I'm getting a one to many match on the rows I'm trying to delete from. So? SQL Server never complained about it. There are many cases where we do a join on tables with 1 to many relationships and do deletes/updates.

But Netezza doesn't like it. You can of course rewrite the query to have an IN clause. But there's also another funky way (at least funky to me) to achieve the desired results:


update tableA A
set col1 = 0
from tableB B
where A.id = B.bid
and b.name = 'x'

For a 3 table join:

update tableA f
set msid = 0
from tableB d, tableC t
where A.p_sid = D.d_sid
and A.t_sid = t.t_sid
and D.day_start = '2012-01-03'
and T.time_minute = 10



15 comments:

  1. Well, I am in the same case... first time I work on Netezza... I have to adapt a DB2 loading script into Netezza stored procedure... and it is just amazing the difference about UPDATE... Thanks a lot to share your experience

    ReplyDelete
  2. Yes, I've been using Netezza for about 2 years now and for the most part it's been great (and fast!). Every now and then small things like this update statement still trips me up but I can live with it.

    ReplyDelete
  3. Why not just use distinct on table B?:

    update tableA
    set col1 = 0
    from tableA A
    join (SELECT DISTINCT bid FROM tableB WHERE name = 'x') B
    on A.id = B.bid

    ReplyDelete
    Replies
    1. Scratch that. I just tried it, and the error is because I have multiple rows on the table I'm trying to update. Apparently, Netezza update statements don't like to use JOIN syntax. I guess we'll just have to use the older syntax for joins in updates.

      Delete
  4. create temp table customer_all_update as
    select b.* ,
    row_number() over(partition by customer_dim_id order by customer_dim_id) as upd_cntr
    from customer_all a, customer_dex b
    where a.customer_dim_id =b.customer_dim_id;

    update customer_all a
    set customer_company_cd = b.customer_company_cd
    from customer_dex b
    where a.customer_dim_id =b.customer_dim_id and upd_cntr=1;

    ReplyDelete
  5. update tableA A
    set col1 = 0
    from tableB B
    where A.id = B.bid
    and b.name = 'x'

    I used the above syntax and still getting the same error . any suggestions would be appreciated.

    Thanks,
    Brajendra

    ReplyDelete
  6. Great Post! Worked for me!

    ReplyDelete
  7. it worked for me, thank you

    ReplyDelete
  8. @Brajendra, in your query shown, use set A.col1 = 0 (You missed the A. reference for col1)

    ReplyDelete
  9. Thanks for give me this information really this product is very effective.

    Nexus 5 Phone Cases and Covers

    ReplyDelete
  10. Thanks alot...It Worked for me

    ReplyDelete
  11. I need below thing exact in Netezza , how it will be in netezza. can anyone share the script?
    BEGIN TRANSACTION A 
    SELECT 'BEFORE UPDATE', * FROM Table
    UPDATE Table
    SET column_1= REPLACE(column_1, '_', ' ')
    SELECT 'AFTER UPDATE', * FROM Table 
    ROLLBACK TRANSACTION A

    ReplyDelete
  12. Great post. I have also worked in sql server for about 6 years and just moved to netezza and the syntax was a headache initially.
    Its pretty fast in my opinion and you would love it if you just know the proper syntax.

    ReplyDelete