New answers tagged

4

I did manage to do this through SQL. However, I think that this is a task that is far better better suited for TRIGGERs - i.e. if a record insertion/deletion is made on the project_map table, then a TRIGGER should fire adding +/- 1 to the relevant company's project field. I gave this a +1 because it was trickier than it seems initially. Giving the DDL was ...


-1

If you do not have space limitation, you can create a new table, the same as your table with your new column added to that table and delete the old table: create new_table as select old_table.*, (with or without default_Value) as new_column from old_table;


2

Your subquery is uncorrelated and returns all rows from the join between native_name_aliases and dpoint, where only a single row would be allowed. I guess this is what you are after: UPDATE dpoint d SET native_name = nna.native_name FROM native_name_aliases nna WHERE d.building_id = 42 AND d.dpoint_id = nna.dpoint_id AND d.native_name IS ...


2

For Postgres 9.4+: UPDATE trajs t SET traj_id = upd.traj_id FROM ( SELECT id, 1 + count(*) FILTER (WHERE t >= t0 + interval '6 min') OVER (PARTITION BY obj_id ORDER BY t) AS traj_id FROM ( SELECT id, obj_id, t, lag(t) OVER (PARTITION BY obj_id ORDER BY t) AS t0 FROM trajs ) sub ) upd WHERE t.id ...


4

Assuming that you want - if the max(number) in the whole table is say, 27 - to update all the rows with NULL, to 28. You need a subquery or a derived table to first find this max and then join back to the table: UPDATE tableX AS t CROSS JOIN ( SELECT MAX(number) AS max_number FROM tableX ) AS m SET t.number = m.max_number + 1 WHERE t....


2

update dbo.B set b.[ID] = a.orig_id FROM dbo.A a join dbo.B b on b.[NUMBER] = a.number and b.[ID] <> a.orig_id the <> is so it can avoid taking a lock if the are equal


0

Remember that size often have a tipping point. ie What works for < 80M rows doesn't necessarily work for > 80M rows. Were you able to split the update into smaller batches? ie. 50K rows each


4

I suspect the issue appears because you are referencing the view twice in the UPDATE statement, once in UPDATE [dbo].[vwMP_VOL] clause and then in FROM clause and the second time you are giving it an alias. But you are not using the alias in the SET clause, only the first reference. The result is that the the two references are unrelated and the rows are ...


1

Change your update to UPDATE [dbo].[vwMP_VOL] SET [vwMP_VOL].[id_batch] = inserted.id_batch ,[vwMP_VOL].[id_invoice] = inserted.id_invoice ,[vwMP_VOL].[text_vchr] = inserted.text_vchr ,[vwMP_VOL].[amt_frt_tcur] = inserted.amt_frt_tcur ,[vwMP_VOL].[amt_line_hcur] = inserted.amt_line_hcur ,[vwMP_VOL].[line_total_hcur] = ...


5

UPDATE dbo.[User] SET username_column = N'ACCOUNTS' + LOWER(SUBSTRING(username_column, 9, 4000)) WHERE username_column LIKE 'ACCOUNTS\%';


1

Replace and Lower should do the job here. UPDATE dbo.User SET username = 'ACCOUNTS\' + LOWER(REPLACE(username, 'ACCOUNTS\', ''))


2

-- Create some working space: CREATE TEMPORARY TABLE t ( productname ..., text ... ); -- copy all the .CSV into it: LOAD DATA INFILE INTO t ... .csv ...; -- do the Update en masse: UPDATE ForgeRock JOIN t ON t.productname = ForgeRock.productname SET ForgeRock.text = t.text; Assuming that nothing changes from run to run, exactly the same ...


0

It seem that your event scheduler is not running causing the problem of event not getting executed. use following command to check show global variables like 'event_scheduler'; if you get that it is off, you can do if you are in MySQL 5.6 or higher set global event_scheduler=1; or add the following line in cnf file to make this change permanent and ...


0

Let's look at how this runs. (Keep in mind that the NOT EXISTS is in the SELECT.) INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ... First, it performs the SELECT, which delivers some number of rows, possibly zero. (Note that the NOT EXISTS may cause it to deliver zero rows.) Second, each of those rows (if any) is checked for duplicates in any ...


1

In this case you can use INSERT INTO ... SELECT FROM ... ORDER BY DESC LIMIT 1: INSERT INTO data_2 (name, text1, text2) SELECT name, serial, CAST(point AS varchar(10)) FROM data_1 ORDER BY point DESC LIMIT 1


4

I have an oracle database table with date format as 'mm/dd/yyyy' No you don't.. ;) You have an Oracle database table with a date datatype. Period. Any type of "format" is for display only, and is easily modified. SQL> create table junk ( id number, dd date ); Table created. SQL> insert into junk values ( 1, sysdate ); 1 row created. ...


2

The problem in your query is this part: ON DUPLICATE KEY UPDATE -- paramvalue = t.extension ; The alias t is not visible in that part of the query. However, assuming that the two values, the one to be inserted in the params.extension column (12345) and the one checked against the params_extensions column (WHERE t.extension = '12345') are the ...



Top 50 recent answers are included