There are two tables Zakazi and Isp_zak

In the table Zakazi trace. columns: id_table, kod_kl, pod_kod_exped, kod_kl_grotpr, u_kod, vesgr, count_vag, CodeGruN, vocode, period_per, dt_zak, stcode, nnod, kod_jd, zak_comlite, smgs

In the Isp_zak table, the following columns: id_table, pod_kod_exped, nomot, nomvag, kodgr, dt_otpr, stotpr, dvydsd, stnds, abr_val, vesgr, uni_id, messvid, id_table_zak

It is necessary to write UPDATE for the Isp_zak table for the id_table_zak field with data from the zakazi table from the id_table field. Provided that the number of duplicate values ​​of id_table orders does not exceed the value of count_vag from the Zakazi table?

With the implementation of these conditions, everything is simple Zakazi.pod_kod_exped = Isp_zak.pod_kod_exped and Zakazi.CodeGruN = Isp_zak.kodgr and Zakazi.dt_zak <Isp_zak.dt_otpr

  • one
    @ E-Sergey, please clarify your question, because it includes several conditions that are understandable only to you - Serge Nazarenko
  • one
    UPDATE iz SET iz.Isp_zak = z.id_table FROM Isp_zak AS iz INNER JOIN (SELECT pod_kod_exped, CodeGruN, dt_zak, count (id_table) OVER (PARTITION BY id_table) AS [number of duplicate id_table values], count_vag FROM / Zakai .pod_kod_exped = iz.pod_kod_exped and z.CodeGruN = iz.kodgr and z.dt_zak <iz.dt_otpr WHERE z. [number of duplicate id_table values]> = z.count_vag - Serge Nazarenko
  • @SergeNazarenko In the Zakazi table, the count_vag field is the number of containers. And in the Isp_zak table, the data on the orders submitted are indicated, but for the 1st container. It is necessary to compare this data. Those. Add order id to certain records in the table executed, but the number of containers corresponding to one order should not exceed specified in the order. - E. Sergey
  • @ e-sergey I correctly understand that the update is needed only in those rows of the Isp_zak table, where the number of nomvag does not exceed the number of count_vag from the Zakazi table for the corresponding order? - Serge Nazarenko
  • @SergeNazarenko Yes - E. Sergey

1 answer 1

This query will work on versions of SQL Server 2008 and above.

UPDATE iz SET iz.Isp_zak = z.id_table FROM Isp_zak AS iz INNER JOIN ( SELECT pod_kod_exped ,CodeGruN ,dt_zak ,count(id_table) OVER (PARTITION BY id_table) AS [количество повторяющихся значений id_table] ,count_vag FROM Zakazi ) AS z ON z.pod_kod_exped = iz.pod_kod_exped and z.CodeGruN = iz.kodgr and z.dt_zak < iz.dt_otpr WHERE z.[количество повторяющихся значений id_table] >= z.count_vag