spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories

Update Not working properly in Netezza. This is actually a general topic


The issue here is that you are joining to RPT_EMAIL_CATEGORY_PREFERENCE twice. You may not realize it because the join with the table specified to update is implicit.

UPDATE RPT_EMAIL_CATEGORY_PREFERENCE  
--  ^ First reference to
RPT_EMAIL_CATEGORY_PREFERENCE (with no alias)
SET CVALIDEMAIL = ROUND(S.new_CVALIDEMAIL,0)
FROM SNAPDATE_YOS S 
-- Which is then joined to SNAPDATE S with NO join
criteria, making it a cross join producing
-- 1 x 34 rows
JOIN RPT_EMAIL_CATEGORY_PREFERENCE P ON
P.PREFERENCE_ID = S.PREFERENCE_ID 
-- The third join then joins 1 row from
RPT_EMAIL_CATEGORY_PREFERENCE with no join
criteria
-- other than a WHERE clause which makes the
output to 1 x 34 x 1 rows.
-- This is because the
RPT_EMAIL_CATEGORY_PREFERENCE when referenced with
a different alias
-- is treated as a separate table.
WHERE P.SNAPSHOTDATE = '2014-11-21 00:00:00'
AND P.PREFERENCE_ID = 'Yosemite';

The UPDATE I think you want is:

UPDATE RPT_EMAIL_CATEGORY_PREFERENCE 
SET CVALIDEMAIL =
ROUND(SNAPDATE_YOS.new_CVALIDEMAIL,0)
FROM SNAPDATE_YOS
WHERE 
RPT_EMAIL_CATEGORY_PREFERENCE.PREFERENCE_ID =
SNAPDATE_YOS.PREFERENCE_ID 
AND RPT_EMAIL_CATEGORY_PREFERENCE.SNAPSHOTDATE =
'2014-11-21 00:00:00'
AND PRPT_EMAIL_CATEGORY_PREFERENCE.PREFERENCE_ID =
'Yosemite';

I removed the aliases for clarity (opinions may vary as to whether that's helpful or not in this case). You should only reference the table being UPDATEd once. For Netezza, inner joins in an UPDATE are implicitly specified by the FROM and WHERE clauses.


Categories : SQL

Related to : Update Not working properly in Netezza. This is actually a general topic
How use CASE syntac in Stored Procedure on Netezza
Try this: create or replace procedure SP_CREATE_TMP_F_WIFIID_AP_UTILISASI() LANGUAGE NZPLSQL RETURNS INT4 AS BEGIN_PROC DECLARE BEGIN /* CREATE TABLE TMP AP UTILISASI WITHOUT CLIENT */ EXECUTE IMMEDIATE ' create TABLE TMP_F_AP_UTILISASI as select AVG(ANN.THROUGPUT_V) as AVG_THROUGHPUT , SUM(ANN.BYTES_SENT) AS SUM_SENT , SUM(ANN.BYTES_RECIEVE) AS SUM_R

Categories : SQL
find column number in a SQL table in IBM netezza database
You can get that information by joining the _v_table and _v_relation_column views. SELECT tablename, attname AS COL_NAME, b.FORMAT_TYPE AS COL_TYPE, attnum AS COL_NUM FROM _v_table a JOIN _v_relation_column b ON a.objid = b.objid WHERE tablename = 'TEST1' ORDER BY attnum; TABLENAME | COL_NAME | COL_NUM -----------+----------+--------- TEST1 | COL1 |

Categories : SQL
Can't Drop Table Cross Database in Procedure Netezza
Operations that write to the database or database catalog for DBB cannot be executed from DBA, and this includes DROP TABLE. When you run this command and it succeeds: DROP TABLE DBB.USR3.STG_AP_LOC_KMEANS_OUT; then I have to believe that you must not be attached to DBA. Are you running your test commands from Aginity, NZSQL, or another tool? Here is how the system should behave. [nz@netezz

Categories : SQL
find partial duplicated rows in a SQL table in IBM netezza database
The following query produces the output you want. Is this what you want to do? SELECT id1, id2, COUNT(*) AS value FROM myTable GROUP BY id1, id2; EDIT: If you want complete duplicates (of all columns) but only to show the first two: SELECT id1, id2, COUNT(*) as value FROM myTable GROUP BY id1, id2, id3, id4, id5, id6; You can add having count(*) > 1 if you only want examples with du

Categories : SQL
error of finding distinct cobinations of muiltiple columns in IBM netezza SQL table
If you first write a query to generate the unique combinations, then you can use it as a nested sub-query. Then it's a simple GROUP BY and COUNT(). SELECT id1, id2, COUNT(*) AS value FROM ( SELECT id1, id2, id3, id4, id5, id6 FROM myTable GROUP BY id1, id2, id3, id4, id5, id6 ) AS uniques GROUP BY id1, id2

Categories : SQL
Recently Add
SQL command to insert two primary keys into third table
How to do a registerform by ASP.NET VB SQL
Shortest time and trip between two stops across many trip options (GTFS)
Using IN and EXISTS subquery with AND operation instead of OR?
How to select multiple alias columns from the same table
Delete records from a given table
date manipulation in postgresql
PSQL query with join query from a db newbie
Protect LocalDB from user access
Update field with combined contents from other fields
SQL select unique records under certain priority and conditions from multiple columns
Multiply quantities for all parent child relationships
I'm having an issue running a query from a batch file
Concatenate strings while filtering by another column in SQL Server view
Parsing Dynamic XML to SQL Server tables with Parent and child relation
Using MERGE for delete and insert in oracle
how to display the pre value and post value of a column after updating it?
sql loop to fill up date between two time stamps
With SQL/TSQL, how can I use STUFF within this WHILE clause?
Challenging db table and query
Query to fetch row corresponding to Max value in SQL
Oracle SQL - ORA-00936 on DATE()
how to split single row into multiple row in db2?
Combine fields into one column
How can I perform an SQl where query then list properly?
SQL - Selection of the oldest order for every customer (join two tables)
Sum two counts in a new column without repeating the code
Creating multiple sum fields based upon criteria in another field in MS access 2007
Grouping by two values in same table
MS SQL last record
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.