This is called a correlated update UPDATE table1 t1 SET (name, desc) = (SELECT t2.name, t2.desc FROM table2 t2 WHERE t1.id = t2.id) WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t1.id = t2.id ) Assuming the join results in a key-preserved view, you could also UPDATE (SELECT t1.id, t1.name name1, t1.desc desc1, t2.name … Read more
this script cleans all views, SPS, functions PKs, FKs and tables. /* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = ‘DROP PROCEDURE … Read more
Do a SELECT COUNT(*) FROM … query instead. OR int size =0; if (rs != null) { rs.last(); // moves cursor to the last row size = rs.getRow(); // get row id } In either of the case, you won’t have to loop over the entire data.
Have you tried: result = db.engine.execute(“<sql here>”) or: from sqlalchemy import text sql = text(‘select name from penguins’) result = db.engine.execute(sql) names = [row[0] for row in result] print names Note that db.engine.execute() is “connectionless”, which is deprecated in SQLAlchemy 2.0.
If you wanted to simply assign some variables for later use, you can do them in one shot with something along these lines: declare @var1 int,@var2 int,@var3 int; select @var1 = field1, @var2 = field2, @var3 = field3 from table where condition If that’s the type of thing you’re after
You have to set a default value. ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL DEFAULT ‘foo’; … some work (set real values as you want)… ALTER TABLE mytable ALTER COLUMN mycolumn DROP DEFAULT;
They are essentially equivalent to each other (in fact this is how some databases implement DISTINCT under the hood). If one of them is faster, it’s going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking … Read more
You can also use update … from syntax and use a mapping table. If you want to update more than one column, it’s much more generalizable: update test as t set column_a = c.column_a from (values (‘123’, 1), (‘345’, 2) ) as c(column_b, column_a) where c.column_b = t.column_b; You can add as many columns as … Read more
UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it — completely different operations. Trivial example of UNION: mysql> SELECT 23 AS bah -> UNION -> SELECT 45 AS bah; +—–+ | bah | +—–+ | 23 | | 45 | +—–+ 2 rows in set (0.00 sec) … Read more
Yes, you can enforce the constraint only when the value is not NULL. This can be easily tested with the following example: CREATE DATABASE t; USE t; CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child (id INT NULL, parent_id INT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ) ENGINE=INNODB; … Read more